I am looking VBA code, which uses cell value "End" as row number

[motilulla]

Hello,

Here is a code below in which I want to change row number as per cell value find in the column A (Value = End)
First instance in column A "End" row number is 2 so far ABC = Range("B2:E2").Value
Second time in column A "End" row number is 10 so far ABC = Range("B10:E10").Value

And this way keep changing till last row numbers 2, 10, 14, 21, 28 and 36

Sub EndRowValue()
  
   Dim ABC As Variant
  
   ABC = Range("B2:E2").Value
  
End Sub

*	A	B	C	D	E
1
2	End
3
4
5
6
7
8
9
10	End
11
12
13
14	End
15
16
17
18
19
20
21	End
22
23
24
25
26
27
28	End
29
30
31
32
33
34
35
36	End

For example the sample image is attached.

Kind Regards
Moti

 

[Marc L]

Hi, a VBA Range demonstration as a very beginner starter :​

Sub Demo1()
           Dim Rf As Range, R&, V
    With ActiveSheet.UsedRange.Columns(1)
           Set Rf = .Find("End", , , xlWhole)
        If Not Rf Is Nothing Then
                R = Rf.Row
            Do
                V = [B:E].Rows(Rf.Row).Value2
              ' …
                Set Rf = .FindNext(Rf)
            Loop Until Rf.Row = R
                Set Rf = Nothing
        End If
    End With
End Sub

 

[motilulla]

Hi, a VBA Range demonstration as a very beginner starter :​

Sub Demo1()
           Dim Rf As Range, R&, V
    With ActiveSheet.UsedRange.Columns(1)
           Set Rf = .Find("End", , , xlWhole)
        If Not Rf Is Nothing Then
                R = Rf.Row
            Do
                V = [B:E].Rows(Rf.Row).Value2
              ' …
                Set Rf = .FindNext(Rf)
            Loop Until Rf.Row = R
                Set Rf = Nothing
        End If
    End With
End Sub

Marc L, thank you for the reply I tried a lot but do not understand how can I apply in my code so each time find text "End" in my given formula change only the "Row Number"

My formula is ABC = Range("B2:E2").Value, I want when text "End" Find in the column C instead of A than in the formula ("B" & Row number , "E" & Row number) keep changing as per text "End" find in the cell and converted it in to the row number in the VBA Range formula)

Is there any easy solution please suggest

Kind Regards
Moti

 

[Marc L]

​

Change the column reference - Columns(1) - according to the used range and obviously Rf.Row is the row number …​

​

Use my codeline rather than your formula …​

​

 

[motilulla]

​

Change the column reference - Columns(1) - according to the used range and obviously Rf.Row is the row number …​

​

Use my codeline rather than your formula …​

​

Marc L, thank you for the tip and help, now it worked fine

Have a excellent start of the week.

Kind Regards
Moti