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

Vishesh's picture

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.

Vishesh's picture

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.