Simple replace value with a certain value by column macro

[peterhui50]

Hi,

I'm trying to create a macro that will replace all the "1"s in a column with a value in Range "A1", then move onto column 2 and replace it with the value in Range "B1" and so on.

this is what I have so far

Sub Macro2()
'
' Macro2 Macro

 Dim X As Integer
 X = InputBox("What column number?")
 Y = InputBox("Where is the value?")
 
Worksheets("Sheet1").Columns(X).Replace _
 What:=1, Replacement:=Range(Y).Value, _
 SearchOrder:=xlByColumns, MatchCase:=True
End Sub

As you can see, it requires the user to put the column number and the location of the value, it works but just not efficient at all.

I"m looking to just loop through a range of columns and the value of it's replacement will move along with it.

Any help is very much appreciated, I have several hundred of these columns and tried to do this in PQ but it gives another problem.

 

[Joe4]

Welcome to the Board!

Try this:

Sub MyReplaceMacro()

    Dim lc As Long
    Dim c As Long
    Dim x As Variant
    
    Application.ScreenUpdating = False
    
'   Find last column in row 1 with data
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Loop through each column
    For c = 1 To lc
'       See if anything in row 1
        If Cells(1, c) <> "" Then
'           Capture value from row 1
            x = Cells(1, c).Value
'           Replace all non-blank cells in column with value
            Columns(c).SpecialCells(xlCellTypeConstants, 23).Value = x
        End If
    Next c
    
    Application.ScreenUpdating = True
    
End Sub

 

[peterhui50]

Welcome to the Board!

Try this:

Sub MyReplaceMacro()

    Dim lc As Long
    Dim c As Long
    Dim x As Variant
   
    Application.ScreenUpdating = False
   
'   Find last column in row 1 with data
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
   
'   Loop through each column
    For c = 1 To lc
'       See if anything in row 1
        If Cells(1, c) <> "" Then
'           Capture value from row 1
            x = Cells(1, c).Value
'           Replace all non-blank cells in column with value
            Columns(c).SpecialCells(xlCellTypeConstants, 23).Value = x
        End If
    Next c
   
    Application.ScreenUpdating = True
   
End Sub

worked like a charm! thank you!

 

[Joe4]

You are welcome.
Glad I was able to help!