Macro for Couning on Blank columns

[absamant]

I recieve numerous data sheets where the number of columns the data runs into varies. For Eg City A Data runs till column X and city b data runs into column AA.

As part of my analysis i require to check the diffrences in my last three columns
eg: x2-w2, y2-x2 and z2-y2. However I cannot recored a comon macro for this as the no of columns keeps changing with every sheet.

Would love if someone would help out with a macro which would calculte the last column till which data is populated and
then paste the formula to calculate the difference in the last three columns

Thanks
Ameya

 

[absamant]

facing a peculiar situation, now, after the changes you suggested the macro works perfectly for the first instace, how ever if i run it again on another sheet, the formulas are pasted about 5 coumns away from the last column. Have tested it many times,only the first instance runs correctly,please help

 

[Andrew Poulsom]

Please post the code you are using that causes this problem.

 

[absamant]

Sub Test()
Dim Sh As Worksheet
Dim LastCol As Integer
Dim Lastrow As Integer
Set Sh = ActiveSheet
With Sh
LastCol = Cells(14, .Columns.Count).End(xlToLeft).Column
Lastrow = Cells(.Rows.Count, 1).End(xlUp).Row
.Range(.Cells(14, LastCol + 1), .Cells(Lastrow, LastCol + 3)).FormulaR1C1 = "=RC[-3]-RC[-4]"
.Cells(1, LastCol + 1).EntireColumn.Insert
End With
End Sub

 

[Andrew Poulsom]

The code uses the ActiveSheet. I inadvertently missed a couple of dots:

LastCol = Cells(14, .Columns.Count).End(xlToLeft).Column
Lastrow = Cells(.Rows.Count, 1).End(xlUp).Row

should be:

LastCol = .Cells(14, .Columns.Count).End(xlToLeft).Column
Lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row

but I don't think that will make a difference.

If you run it more than once on the same sheet it won't work as intended because LastCol will have increased by the addition of the formulas. But it should work on a "virgin" sheet.

 

[absamant]

thanks a ton Andrew, think the stuff is finally working, will keep you posted if any errors occur

 

[absamant]

hi, is there a way i can add a conditional format to the range being calculated from the data in this macro. i need numbers above and below a certain limit to be highlihghted in red.