use row number as reference for edit value in a row VBA

[quarna]

Hi.

I have this line that gives me a row number on multiple sheets (if value is found)

Set flg = ws.Range("Tabel" & silonr).Find(What:=id, LookIn:=xlValues, Lookat:=xlPart, searchorder:=xlByRows, _
    searchdirection:=xlNext, MatchCase:=False, searchformat:=False)

flg will be $B$196 for instance, and it gives me some values on this line.
I want to use flg to edit those value and return them to the same line.


But i cannot find out how to use $B$196 for the row reference.

thank you

 

[Phuoc]

Set flg = ws.Range("Tabel" & silonr). Find(What:=id, LookIn:=xlValues, Lookat:=xlPart, searchorder:=xlByRows, _
    searchdirection:=xlNext, MatchCase:=False, searchformat:=False)
Myrow = flg.row

 

[quarna]

Set flg = ws.Range("Tabel" & silonr). Find(What:=id, LookIn:=xlValues, Lookat:=xlPart, searchorder:=xlByRows, _
    searchdirection:=xlNext, MatchCase:=False, searchformat:=False)
Myrow = flg.row

i see what you mean.

But forgot to write that it is not in the same sub.
the value $B$196 is in a cell, and i want to use that as reference row, so i can put a .range value to that row ($B$196) and overwrite some values.

like .range(B196:N196)

 

[quarna]

Function tilføjFlgTilFaner(shname As String, myrow As Range, r As Integer)

With Worksheets(shname)
    .Range(myrow).Offset(0, 0) = fugter.Cells(r, 4) 
    .Range(myrow).Offset(0, 1) = fugter.Cells(r, 5) 
    .Range(myrow).Offset(0, 2) = fugter.Cells(r, 6) 
    .Range(myrow).Offset(0, 3) = fugter.Cells(r, 7) 
    .Range(myrow).Offset(0, 4) = fugter.Cells(r, 8) 
    .Range(myrow).Offset(0, 5) = fugter.Cells(r, 9)
    .Range(myrow).Offset(0, 6) = fugter.Cells(r, 10)
    .Range(myrow).Offset(0, 7) = fugter.Cells(r, 11) 
    .Range(myrow).Offset(0, 8) = fugter.Cells(r, 12) 
    .Range(myrow).Offset(0, 9) = fugter.Cells(r, 13)
    .Range(myrow).Offset(0, 10) = fugter.Cells(r, 14) 
    .Range(myrow).Offset(0, 11) = fugter.Cells(r, 15)
    .Range(myrow).Offset(0, 12) = fugter.Cells(r, 27) 
End With

End Function

i got this workaround to work, i could imagine there is a cleaner way, but it gets the job done on 13 sheets.
myrow is the $B$196 cell value.

thanks

 

[Alex Blakenburg]

It sounds like you just want something like this:

Myrow = Range(flg.Value).Row