I found some VBA code that works for me, except for one line, and am wondering if someone can give me a suggestion.
In the below code, instead of manually designating how many rows to go down (+29) , I have a formula that calculates the # rows to go down in cell B2 on sh1. ( formula to calc because the range changes all the time). Can I have the VBA just reference the value that is returned from the formula in B2?
Dim strFind As String
Dim fRow, fCol As Integer
Dim sh1, sh2 As Worksheet
Set sh1 = Sheets("Inv5")
Set sh2 = Sheets("Inv1")
strFind = sh1.Range("N5").Value
Set foundCell = sh2.Range("C:C").Find(strFind, LookIn:=xlValues)
If Not foundCell Is Nothing Then
fRow = foundCell.Row
fCol = foundCell.Column
sh2.Range(Cells(fRow, fCol + 10).Address & ":" & Cells(fRow + 29, fCol).Address).Copy
sh1.Range("R10").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Else
Call MsgBox("Not found the match cell!", vbExclamation, "Finding String")
End If
In the below code, instead of manually designating how many rows to go down (+29) , I have a formula that calculates the # rows to go down in cell B2 on sh1. ( formula to calc because the range changes all the time). Can I have the VBA just reference the value that is returned from the formula in B2?
Dim strFind As String
Dim fRow, fCol As Integer
Dim sh1, sh2 As Worksheet
Set sh1 = Sheets("Inv5")
Set sh2 = Sheets("Inv1")
strFind = sh1.Range("N5").Value
Set foundCell = sh2.Range("C:C").Find(strFind, LookIn:=xlValues)
If Not foundCell Is Nothing Then
fRow = foundCell.Row
fCol = foundCell.Column
sh2.Range(Cells(fRow, fCol + 10).Address & ":" & Cells(fRow + 29, fCol).Address).Copy
sh1.Range("R10").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Else
Call MsgBox("Not found the match cell!", vbExclamation, "Finding String")
End If