Find value, copy cell contents and paste to adjacent column (RESOLVED)
Need help to change the following Excel Macro (VB) code to perform find of value $ in Column A,then copy to ColumnB and fill down where there is used cell data in Column A until Blank cell is reached. There are 1703 occurrences of $ in Column A and a total line count of 60959 (includes variable data and blank empty cells). I have also attached a small sample of what I am seeing with two different options for what I'd like to see as my output. You can assume that value $ is the header and it needs to be prefixed to the data below it, until another occurrence of $ is reached. I have put the headers in "Bold" for this example.
Here is the code that I have so far:
Range("A1:A60959").Select
Selection.Find(What:="$", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Range("A2").Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B2:B5"), Type:=xlFillCopy
Range("B2:B5").Select
Range("B5").Select
Cells.Find(What:="$", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub
Try the following code
Try the following code instead...put this code in a procedure within 'Sub' and 'End Sub'.
Dim rngData As Range
Dim arr
Dim strSS As String
Dim wks As Worksheet
Dim x As Long
Set wks = ThisWorkbook.ActiveSheet
Set rngData = wks.UsedRange.Resize(, 3)
arr = rngData
strSS = ""
For x = LBound(arr, 1) To UBound(arr, 1)
If InStr(arr(x, 1), "$") > 0 Then
strSS = arr(x, 1)
arr(x, 2) = strSS
arr(x, 3) = strSS
End If
If Not IsEmpty(arr(x, 1)) And strSS <> "" And InStr(arr(x, 1), "$") = 0 Then
arr(x, 2) = strSS
arr(x, 3) = strSS & "." & arr(x, 1)
End If
Next x
rngData = arr
Set rngData = Nothing
Set wks = Nothing
Erase arr
Thank you
That code worked perfectly. Why is it that the macro sample that I sent would not work? I appreciate your help, you saved me a lot of time.
What you have done I think is
What you have done I think is recorded a macro which works in simple straight processes. But for complex process code needs to be modified.