I want my formula to fit the target cell
Good day,
I want this code to follow the target cell (G20) even if I have inserted multiple lines before line 20
=LEFT(G20,3)&TEXT(COUNTIF(G20:G20,LEFT(G20,1)&"*"),"000")
Thank,
Can I also set the INDRECT code for the Cell D20?
Private Sub CommandButton1_Click()
Dim x As String, rcell As Range, y As String, z As Long
x = "RFP" & Format(Now, "mmddyyhhmmss")
y = Left(x, 10)
z = Right(x, 2)
For Each rcell In Range("D20:D" & Range("D" & Rows.Count).End(3).Row)
If Cells(rcell.Row, 1) = "" Then Cells(rcell.Row, 1) = y & z
z = z + 1
Next rcell
End Sub
source to share
Try to surround the G20 with $ 2 signs. Like this:
=LEFT($G$20,3)&TEXT(COUNTIF($G$20:$G$20,LEFT($G$20,1)&"*"),"000")
Read a little about dollar signs here:
https://blogs.office.com/en-us/2011/08/17/making-sense-of-dollar-signs-in-excel/ in general they are really helpful.
source to share
If you always want the formula to refer to Cell G20
even after inserting the lines above, use a INDIRECT
function like
=LEFT(INDIRECT("G20"),3)&TEXT(COUNTIF(INDIRECT("G20:G20"),LEFT(INDIRECT("G20"),1)&"*"),"000")
NOTE. INDIRECT
- volatile function. See this one for details .
source to share