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

 

[Zerb]

Got it, I think, with all of your help.

I'm not going to use the If then Else statements because I do not care if the code doesn't find anything at this time.

I was getting turned around with thinking I needed the " " in the range ( ) as well as needing a variable in there and not just the StatusCol variable.

Dim StatusCol As Range

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

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

 

[Zerb]

Now if that StatusCol.Address is say "C5" and I want to now copy from B3:G5, how would I go about that? I can get the offset on the first part I want to copy by the code that I pasted above, but I can't figure out how to get the second cell value of the range I'm looking to copy.

 

[DanteAmor]

I'm not going to use the If then Else statements because I do not care if the code doesn't find anything at this time

You must use at least the if, because if it does not find the data, the macro sends an error. And don't forget to put the parameters in the Find method either.

  Dim StatusCol As Range
  
  Set StatusCol = Worksheets("Testing").Range("C3:C500").Find("ABC123", , xlValues, xlWhole, , , False) 'Do not ignore the parameters
  If Not StatusCol Is Nothing Then
    Worksheets("Testing").Range(StatusCol.Address).Offset(1, -1).Copy
    Worksheets("Testing1").Range("B500").End(xlUp).Offset(1).PasteSpecial
  End If

 

[DanteAmor]

Now if that StatusCol.Address is say "C5 and I want to now copy from B3:G5

How about:

Sub tesxt()
  Dim StatusCol As Range
  
  Set StatusCol = Worksheets("Testing").Range("C3:C500").Find("ABC123", , xlValues, xlWhole, , , False) 'Do not ignore the parameters
  If Not StatusCol Is Nothing Then
    StatusCol.Offset(-2, -1).Resize(3, 6).Copy
    Worksheets("Testing1").Range("B500").End(xlUp).Offset(1).PasteSpecial
  End If
End Sub

 

[Zerb]

You must use at least the if, because if it does not find the data, the macro sends an error. And don't forget to put the parameters in the Find method either.

  Dim StatusCol As Range
 
  Set StatusCol = Worksheets("Testing").Range("C3:C500").Find("ABC123", , xlValues, xlWhole, , , False) 'Do not ignore the parameters
  If Not StatusCol Is Nothing Then
    Worksheets("Testing").Range(StatusCol.Address).Offset(1, -1).Copy
    Worksheets("Testing1").Range("B500").End(xlUp).Offset(1).PasteSpecial
  End If

Got it

Dim StatusCol As Range

Set StatusCol = Worksheets("Test").Range("C3:C500").Find("ABC123", , xlValues, xlWhole, , , False)
If Not StatusCol Is Nothing Then
Worksheets("Test").Range(StatusCol.Address).Offset(-1,-1).Copy  'How do I get a range value here for the second cell in my range, something like StatusCol.Address.Offset(5,5).Copy is what Im attempting to do
Worksheets("Testing").Range("B500").End(xlUp).Offset(1).PasteSpecial
End If



End Sub

 

[DanteAmor]

How do I get a range value here for the second cell in my range, something like StatusCol.Address.Offset(5,5).Copy is what Im attempting to do

I think you better explain to us what the final goal is.
In your example:
"if that StatusCol.Address is say "C5 "and I want to now copy from B3: G5", I replied in post #14 with property resize

 

[Zerb]

How about:

Sub tesxt()
  Dim StatusCol As Range
 
  Set StatusCol = Worksheets("Testing").Range("C3:C500").Find("ABC123", , xlValues, xlWhole, , , False) 'Do not ignore the parameters
  If Not StatusCol Is Nothing Then
    StatusCol.Offset(-2, -1).Resize(3, 6).Copy
    Worksheets("Testing1").Range("B500").End(xlUp).Offset(1).PasteSpecial
  End If
End Sub

Excellent, had to tweak a bit for my code but the resize worked perfect. Thanks!

 

[Zerb]

I think you better explain to us what the final goal is.
In your example:
"if that StatusCol.Address is say "C5 "and I want to now copy from B3: G5", I replied in post #14 with property resize

That resize was perfect advice, I just was responding to a previous post of yours and didn't see the resize comment before I posted. Thanks!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.