Change every letter to the referencial in column A

Question

I have this spreadsheet that needs to change the values,

A       B   C   D   E   F   G
5       e   e   z   z   u   u
10      e   e   z   z   u   u
20      e   e   z   z   u   u
30      e   e   z   z   u   u
40      e   e   z   z   u   u
50      e   e   z   z   u   u

Well, every letter there needs to change to the referencial in the column A.

Right now, my code is:

Sub Macro1()'


'Line 2
Range("C2:H2").Select
Selection.Replace What:="e", Replacement:=Range("A2").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Selection.Replace What:="z", Replacement:=Range("A2").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Selection.Replace What:="u", Replacement:=Range("A2").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

In the original sheet I have over 300 lines to format. All ranged E7:BQ7. So, I need to make a LOOP, changing the range of selection and also my referencial.

Someone can help with this?

Are your values all in a column? Or a single cell? Instead of find/replacing everything, would a `Vlookup()` work instead? If I read your code correctly, everything is being replaced by "A2"'s value, so why not just put A2's value of that range, instead of find/replacing? Could you expand a little more on what's going to be replaced? — BruceWayne, Oct 22 '15 at 18:12
for each line, loop through the selected cells and put cell value into an array if that value does not exist in the array. Then, loop through the array, using the value as the thing to be replaced — brietsparks, Oct 22 '15 at 18:25
Not clear. Can you show what the example data would look like after the process? — Excel Hero, Oct 22 '15 at 18:26

Scott Holtzman · Answer 1 · 2015-10-22T18:42:54.303

0

If I take your post to mean exactly what you say, this simple loop will make all cells in columns E-BQ equal to the value in column A for each row.

Sub Marco1()

Dim wks As Worksheet
Set wks = Sheets("Sheet1") 'replace with your sheet name

With wks

    Dim lRow As Long
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row 'assumes continuous rows with no blanks in data

    Dim l As Long
    For l = 2 To lRow 'assumes you are starting at row 2

        .Range("E" & l & ":BQ" & l).Value = .Range("A" & l).Value

    Next

End With

End Sub

EDIT

Based on ExcelHero's comment the code below will also work without looping.

With wks

    Dim lRow As Long
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row 'assumes continuous rows with no blanks in data

    .Range("E2:BQ" & l).Value = .Range("A2:A" & l).Value

End With

edited Oct 22 '15 at 18:42

answered Oct 22 '15 at 18:24

Scott Holtzman

27,099
5
37
72

If that is all OP needs, then: [b1:g6] = [a1:a6].Value – Excel Hero Oct 22 '15 at 18:32
great point @ExcelHero -> I admit, I was skeptical that this was really what the OP needed, but I read is question a couple of times and their code, and it appeared pretty straightforward. – Scott Holtzman Oct 22 '15 at 18:37

Change every letter to the referencial in column A

1 Answers1

EDIT