Hi, below is my code for Excel VBA, I want to know how I can make Range("A2:A4886") dynamic meaning when it calculates istext from another column it f

[meetdudhia]

Sub function_macro()
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(ISTEXT(RC[1]),RC[1],IF(ISTEXT(R[-1]C),R[-1]C,))"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A4886")
Range("A2:A4886").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Tickt_No"
Range("A2").Select
Columns("A:A").EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = ""
Range("B2:B4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-18
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:A").EntireColumn.AutoFit
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save

End Sub

 

[VoG]

Try like this

Sub function_macro()
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("A2:A" & LR).FormulaR1C1 = "=IF(ISTEXT(RC[1]),RC[1],IF(ISTEXT(R[-1]C),R[-1]C,))"

 

[328mike]

Dim DataRange As Long 'Counts the number of cells that contain data
    Dim i As Long
    DataRange = Application.WorksheetFunction.CountA(Range("A:A")) - 1
    For i = 1 To DataRange
    
    ...Cells(1 + i, [Your Column Here]) ....Your formula Here...
    Next i

That will dynamically match any data range you define with the data range variable.