Solved: Autofill till cell left of it is empty

[mahsun23]

Dear people,

For a couple of days I am trying to write a macro, but i can't finish it, because I am stuck at the end.

I want to autofill the range below a active cell till the cell of the left cell is empty. Please see my macro below, which is not finished because i need a autofill line below the activecell.select
thank you in advance.

Mahsun



Sub MahsunDogan ()

Range("A1").Select
Cells.Find(What:= _
"#tresgte" _
, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:= _
xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) _
.Activate
Selection.Offset(4, 6) = "=INDEX(R4C44:R6172C48,MATCH(RC[-1],R4C4:R6172C4,0),5)"
ActiveCell.Select
....................
.............
End Sub

[austenr]

something like:

election.AutoFill Destination:=Range.Offset(4, 6).Formula = "=INDEX(R4C44:R6172C48,MATCH(RC[-1],R4C4:R6172C4,0),5)"


untested

[mahsun23]

Thank you for the reply, the formula doesn't work, because I get a message that it doesn't recognize the Range.
See formula below:

Range("A1").Select
Cells.Find(What:= _
"#treasg" _
, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:= _
xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) _
.Activate
Selection.AutoFill Destination:=Range.Offset(4, 6).Formula = "=INDEX(R4C44:R6172C48,MATCH(RC[-1],R4C4:R6172C4,0),5)"
End sub

[shrivallabha]

See if this is what you are after
Sub MahsunDogan()
Dim R As Range
Dim RefCol As Long
Dim LastRow As Long
With ActiveSheet
Set R = .Cells.Find(What:="#treasg", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:= _
xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If R Is Nothing Then
MsgBox "The Specified Formula Not Found in the Current Sheet!"
Else
RefCol = R.Column
LastRow = .Cells(R.Row, RefCol).End(xlDown).Row
For i = R.Row To LastRow
.Cells(i, RefCol).Offset(4, 6).Formula = _
"=INDEX(R4C44:R6172C48,MATCH(RC[-1],R4C4:R6172C4,0),5)"
Next i
End If
End With
End Sub

[mahsun23]

Issue solved!!!! thank you all, see result below.




Sub deelC2()

Dim R As Range
Dim Limit As Long
Set R = Cells.Find(What:="#traagv", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:= _
xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(4, 6)
R.FormulaR1C1 = "=INDEX(R4C44:R6172C48,MATCH(RC[-1],R4C4:R6172C4,0),5)"
Limit = Cells(R.Row, R.Column - 1).End(xlDown).Row
R.AutoFill Range(Cells(R.Row, R.Column), Cells(Limit, R.Column))

End Sub

[shrivallabha]

Your code may error out or won't show any action if the keyword is not present in the worksheet. So to work around the error you can put in some If Else condition.
PS: I tried your code by placing in (Not ThisWorksheet) sheet1 module. And it gave me error 91 Object Variable or With Block variable not set.
Sub deelC2()

Dim R As Range
Dim Limit As Long
Set R = Cells.Find(What:="#traagv", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:= _
xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(4, 6)
R.FormulaR1C1 = "=INDEX(R4C44:R6172C48,MATCH(RC[-1],R4C4:R6172C4,0),5)"
Limit = Cells(R.Row, R.Column - 1).End(xlDown).Row
R.AutoFill Range(Cells(R.Row, R.Column), Cells(Limit, R.Column))

End Sub

Why?

[mahsun23]

Hello shrivallabha,

The code works fine for me, maybe it has to do with the fact that the seach didn't gave any result. The advantage of not putting an If Else, is that you know that something is wrong when it doesn't work.

Take care

Mahsun