How to do cell copy- paste and value substitution in VBA?

Question

I have a table with 2 columns.

A B

A2 B2 nn

A3 B3 nn

.....

An Bn nn

I need to copy the content of B2 cell and paste it to all the other B column cells, where A column has a value. Then to find a certain value (nn) in B column and substitute it with A column value. In order to copy B2 content I do this:

Sub CopyTest()

    'ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Select

    Range("B3:B1048576").Select
    Selection.ClearContents

    Range("B2").Copy
    Range("B2:B7").PasteSpecial (xlPasteAll)

Application.CutCopyMode = False

End Sub

1.The problem is that I don't know how to do a paste not till certain cell (B7), but for all the table (so till A column contains value).

Similar problem I have substituting certain B column value with a value from column A.

Sub ReplaceExample()

    Dim OriginalText As String
    Dim CorrectedText As String

    OriginalText = Range("B2").Value
    CorrectedText = Replace(OriginalText, "E_ONBAL", Range("A2").Value)
    Range("B2").Offset(, 1).Value = CorrectedText



End Sub

2.How to do the same action for all the A column, so to do kind of loop?

Thanks!

This answer may help you: http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920 — Dmitry Pavliv, Feb 21 '14 at 13:55

score 0 · Answer 1 · answered Feb 21 '14 at 14:53

The first part is managed, so I publish the answer, in case it will be useful for someone:

Sub CopyTest()

    Range("B3:B1048576").Select
    Selection.ClearContents

Set currentsheet = ThisWorkbook.Worksheets("Sheet1")
    LastRow = currentsheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Range("B2").Copy
    Range("B3" & ":" & "B" & LastRow).PasteSpecial (xlPasteAll)

Application.CutCopyMode = False

End Sub

I just still did not manage how to modify second part of the script with substitution in order to do the same action for all the A column, so to do kind of loop.

score 0 · Answer 2 · answered Oct 26 '16 at 01:45

This example how to copy from activesheet to another sheet.

Sub Test1()

Dim SuccessSheet As String
Application.ScreenUpdating = False
SuccessSheet = ActiveSheet.Name

WS_Count = ActiveWorkbook.Worksheets.Count


' Generate new sheet if it does not exist
If Not sheetExists(SuccessSheet & " Log") Then
    Set WS = Sheets.Add(After:=Worksheets(WS_Count))
    Worksheets(SuccessSheet).Columns(2).Copy Destination:=Worksheets(WS.Name).Columns(2)
    Worksheets(SuccessSheet).Columns(4).Copy Destination:=Worksheets(WS.Name).Columns(1)

WS.Name = SuccessSheet & " Log"

End if

Application.ScreenUpdating = True
End Sub

How to do cell copy- paste and value substitution in VBA?

2 Answers2