Set Cell.Value = text & dynamic value
 

SheetName = A001
A B C D E F G
1 A001.1 Status:
2
3
4
5
6 A001.2 Status:
7
8
9 A001.3 Status:

I am trying to write macro for setting the value in the B column for
rows only in which G = "Status:"
The value is the SheetName & ".1"
(Then & ".2", Then & ".3" in sequence)

Can anyone help me?
Here is what sets the first value correctly, now I need it to be
dynamic. Thanks!!!!

Sub CellValues()

Dim rStatus As Range
Dim rng As Range

With ThisWorkbook.ActiveSheet
Set rStatus = .Range(.Range("G14"), .Range("G14").End(xlDown))
End With

For Each rng in rStatus
If rng = "Status:" Then
rng.Offset(0, -5).Value = ActiveSheet.Name & ".1"
prevTC = ActiveCell.Value
End If
Next rng

End Sub

Set Cell.Value = text & dynamic value
 

Public Sub ProcessData()
Const TEST_COLUMN As String = "G" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cntr As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, TEST_COLUMN).Value = "Status:" Then
cntr = cntr + 1
.Cells(i, "B").Value = .Cells(i, TEST_COLUMN).Parent.Name &
cntr
End If
Next i
End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"CJ" wrote in message
ps.com...
SheetName = A001
A B C D E F G
1 A001.1 Status:
2
3
4
5
6 A001.2 Status:
7
8
9 A001.3 Status:

I am trying to write macro for setting the value in the B column for
rows only in which G = "Status:"
The value is the SheetName & ".1"
(Then & ".2", Then & ".3" in sequence)

Can anyone help me?
Here is what sets the first value correctly, now I need it to be
dynamic. Thanks!!!!

Sub CellValues()

Dim rStatus As Range
Dim rng As Range

With ThisWorkbook.ActiveSheet
Set rStatus = .Range(.Range("G14"), .Range("G14").End(xlDown))
End With

For Each rng in rStatus
If rng = "Status:" Then
rng.Offset(0, -5).Value = ActiveSheet.Name & ".1"
prevTC = ActiveCell.Value
End If
Next rng

End Sub

Set Cell.Value = text & dynamic value
 

Worked perfectly! Thank you!