VBA to find string in cell then use cells value elsewhere in code

[Zerb]

I am having difficulty figuring out how to accomplish this here. I want to search for a string value within in a range of cells. Then if that string value is found use that cells value to base a copy from that sheet to another. The latter portion I know I can do in VBA without much trouble, I can also find the cell that the string value is in, I just can't figure out how to then use that cells value, say its "C10" elsewhere within my VBA code.

I can find the string value in the cell using either of these two methods it seems, but I cannot seem to figure out how to then store that cell value where I found the string in within a variable to that I can reference it later on in the sub within my copy/paste code.

Dim StatusCol As Range
Dim FoundString As Range

Set StatusCol = Worksheets("Test").Range("C3:C500").Find("ABC123")
Set FoundString = 'Add code here that I can't figure out

Dim Status As Range
Dim StatusCol As Range

For Each Status In StatusCol
       
    If Status = "ABC123" Then
'Add code here that I can't figure out
    End If
    
Next Status

 

[DanteAmor]

Try this:

Sub tesxt()
  Dim FoundString As Range
 
  Set FoundString = Worksheets("Test").Range("C3:C500").Find("ABC123", , xlValues, xlPart, , , False)
  'Add code here that I can't figure out
  If FoundString Is Nothing Then
    MsgBox "data not found"
  Else
    FoundString.Copy
    'or
    FoundString.Interior.Color = vbYellow
    'You can do whatever you want with the FoundString object
  End If
End Sub

 

[Engberg]

You could try Range.Address

Sub cellAddress()
 Dim StatusCol As Range

 If StatusCol Is Nothing Then 'Since .Find can end up not finding anything this If-statement is good to have
     MsgBox ("Didn't find a match")
 Else
     Set StatusCol = Range("A1:C3").Find("ABC123")
     Range(StatusCol.Address).Value = "asd" 'One way of refering to that cell again and changing its value
 End If
End Sub

 

[DanteAmor]

 Dim StatusCol As Range

 If StatusCol Is Nothing Then 'Since .Find can end up not finding anything this If-statement is good to have
     MsgBox ("Didn't find a match")

Hi Engberg, that way StatusCol is always on nothing, so it will always send the message.

 

[Zerb]

Maybe I'm not following here but I can run this code

Dim Status As Range
Dim FoundString As Range
Dim StatusCol As Range

Set StatusCol = Worksheets("Testing").Range("C3:C500").Find("ABC123")
Debug.Print StatusCol.Value
Debug.Print StatusCol.Address

If StatusCol Is Nothing Then
'
Else
FoundString = Range(StatusCol.Address)
End If

And I can get the .Value as well as the .Address to print in the immediate window, however I cannot get that .Address into a variable for use later on in my code, as I'm attempting to do with the FoundString variable you see in the code. I want to then be able to refer to that cells value in some VBA like this...

Worksheets("Testing").Range("ADDRESS OF THE CELL THAT I FOUND THE STRING IN").Copy

 

[Engberg]

 Dim StatusCol As Range

 If StatusCol Is Nothing Then 'Since .Find can end up not finding anything this If-statement is good to have
     MsgBox ("Didn't find a match")

Hi Engberg, that way StatusCol is always on nothing, so it will always send the message.

Thanks, Set StatusCol is supposed to be outside of the If-statement

Sub cellAddress()
 Dim StatusCol As Range
 Set StatusCol = Range("A1:C3").Find("ABC123")

 If StatusCol Is Nothing Then 'Since .Find can end up not finding anything this If-statement is good to have
     MsgBox ("Didn't find a match")
 Else
     Range(StatusCol.Address).Value = "asd" 'One way of refering to that cell again and changing its value
 End If
End Sub

 

[DanteAmor]

No other variable is necessary.
Take into account that fewer variables make the code more efficient, so you can use only one variable, put the variable name that you want.

Sub tesxt()
  Dim StatusCol As Range
  
  Set StatusCol = Worksheets("Test").Range("C3:C500").Find("ABC123", , xlValues, xlPart, , , False)
  'Add code here that I can't figure out
  If StatusCol Is Nothing Then
    MsgBox "data not found"
  Else
    Debug.Print StatusCol.Value
    Debug.Print StatusCol.Address
  End If
End Sub

You can use StatusCol later, without needing to put it in another variable.

 

[Zerb]

No other variable is necessary.
Take into account that fewer variables make the code more efficient, so you can use only one variable, put the variable name that you want.

Sub tesxt()
  Dim StatusCol As Range
 
  Set StatusCol = Worksheets("Test").Range("C3:C500").Find("ABC123", , xlValues, xlPart, , , False)
  'Add code here that I can't figure out
  If StatusCol Is Nothing Then
    MsgBox "data not found"
  Else
    Debug.Print StatusCol.Value
    Debug.Print StatusCol.Address
  End If
End Sub

You can use StatusCol later, without needing to put it in another variable.

This is what I am attempting to do in the end and it still seems to not work.

Dim StatusCol As Range

Set StatusCol = Worksheets("Testing").Range("C3:C500").Find("ABC123")

If StatusCol Is Nothing Then

Else
Worksheets("Testing").Range("StatusCol.Address").Offset(1, -1).Copy
Worksheets("Testing1").Range("B500").End(xlUp).Offset(1).PasteSpecial
End If

I would like to use the address that is StatusCol.Address in the Range for the .Copy instruction.

 

[Engberg]

This is what I am attempting to do in the end and it still seems to not work.

Dim StatusCol As Range

Set StatusCol = Worksheets("Testing").Range("C3:C500").Find("ABC123")

If StatusCol Is Nothing Then

Else
Worksheets("Testing").Range("StatusCol.Address").Offset(1, -1).Copy
Worksheets("Testing1").Range("B500").End(xlUp).Offset(1).PasteSpecial
End If

I would like to use the address that is StatusCol.Address in the Range for the .Copy instruction.

That might work if you remove the quotation marks from Range("StatusCol.Address")

 

[DanteAmor]

I would like to use the address that is StatusCol.Address in the Range for the .Copy instruction.

That's one way, But without quotes
Worksheets("Testing").Range(StatusCol.Address).Offset(1, -1).Copy

But it's simpler if you use the object.

Sub tesxt()
  Dim StatusCol As Range
  
  Set StatusCol = Worksheets("Testing").Range("C3:C500").Find("ABC123", , xlValues, xlPart, , , False) '
  If StatusCol Is Nothing Then
    MsgBox "data not found"
  Else
    StatusCol.Offset(1, -1).Copy
    Worksheets("Testing1").Range("B500").End(xlUp).Offset(1).PasteSpecial
  End If
End Sub