meclive
09-01-2008, 05:15 AM
Help! im stuck!
Im trying to get some VBA to search in my worksheet to find a match to the value of cell B5 (the value of B5 changes depending on user choice), then once a match is found I want to highlight from the matched cell to the right 5 columns and down to row 193 (so in total 6 columns would be highlighted down to row 193), then copy and paste special values over those cells.
Any ideas? any help much appreciated! thanks.
My code so far:
Sub test2()
'
' test2 Macro
'
Dim rng As Range
Dim strDate
strDate = Range("B5").Value
Set rng = Range("26:26").Find(What:=strDate, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Not rng Is Nothing Then
With rng.Resize(193 - rng.Row + 1, 6)
.Value = .Value
End With
End If
rng.Select
Selection.Copy
'paste special values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Im trying to get some VBA to search in my worksheet to find a match to the value of cell B5 (the value of B5 changes depending on user choice), then once a match is found I want to highlight from the matched cell to the right 5 columns and down to row 193 (so in total 6 columns would be highlighted down to row 193), then copy and paste special values over those cells.
Any ideas? any help much appreciated! thanks.
My code so far:
Sub test2()
'
' test2 Macro
'
Dim rng As Range
Dim strDate
strDate = Range("B5").Value
Set rng = Range("26:26").Find(What:=strDate, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Not rng Is Nothing Then
With rng.Resize(193 - rng.Row + 1, 6)
.Value = .Value
End With
End If
rng.Select
Selection.Copy
'paste special values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False