Macro is not working properly

Help Studio
, ,
1

Hi All,

there is one macro written vbs and it’s is doing some operation in excel, but when there is a single row in excel and it need to do calculation on specific column and there it is failed to do so.

how to fix it.
inv_VBACode.txt (719 Bytes)

pls do let me know if you need more information.

Thanks in advance
Rakesh

2

@Rakesh_Tiwari

Please show the error…Elso it would good idea to debug the macro from the file and then use it in invoke vba

cheers

3

Hello @Anil_G

image
image798×45 3.75 KB

you can see the above img, in this file there is only one row and bot should calculate Aging and Range column based on invoice date column. but it didn’t do.

FYI - bot is able to do the calculation correctly when there is more than one row available.

Thanks
Rakesh

4

@Rakesh_Tiwari

Okay…so basically your xlup is failing I guess…can you run the macro from excel file and check which line it is failing and what error you are getting

cheers

5

There is no error it seems, but i tried to debug it, and found that here is it is failing

image
image1262×572 22.5 KB

till above it is working fine

6

@Rakesh_Tiwari

I dont think it is failing there it can fail before or after…

because it is just a line to select A1 cell

can you tell what the error is

That lien is done to check if there are filters in first row and remove them…but we can remove that last line anyways…as it is being done at the first already

cheers

7

error is what what you can see in img, it is written as Aging and Range instead value.

shall i try removing last two line and check?

8

same result even after removing last line

9

@Rakesh_Tiwari

Remvoe the select part and the last

cheers

10

you mean to say removing below two lines?

Range(“A1”).Select
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

if yes, then already tested but same result.

11

@Rakesh_Tiwari

Then that means its not failing ont hose lines…please first identify the line which is failing…the we cna modify accordingly

Cheers

12

yes, i tested couple of times, i found that till selection.filldown is working fine

Selection.FillDown

after that it is failing and changing to text(Aging and Range)

13

@Rakesh_Tiwari

use a message box and view the value of lr looks liek lr is taken as 1 …when you have single row and that is creating the issue

cheers

14

i put msgbox right after selection.filldown and i got the value as 2

image

15

@Rakesh_Tiwari

then use

If lr > 2 Then
Range("L2:M" & lr).Select
Selection.FillDown
End If

cheers

16

yes, now working as expected.

Thanks @Anil_G :slight_smile:

1 Like
17

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.