Code for value replacement

[asimoes]

Hi everyone, first post here.

So, 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

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

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

Right now, my code is this

"
Sub Macro1()'
' Macro1 Macro
'


'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
Range("J6").Select
End Sub
"

In the original sheet I have over 300 lines do 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?
Sorry for my bad english.

Thank you!

 

[MARK858]

Probably reading this totally wrong so make sure you test on a copy of your data.

Sub xxxx()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Range("C" & i & ":" & "H" & i).Value = Cells(i, "A").Value
    Next
    Application.ScreenUpdating = True
End Sub

Not sure at all what you mean by

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

are you saying that the range you are using in your macro aren't correct (if not why didn't you put the range you needed) or do you need to do something else?

 

[L. Howard]

Try this in a standard module.

You will have to replace the "e", "z", "u" one at a time, that is, run the code for each separate value to replace.

Adjust the range in this line to match your sheet.
With ActiveSheet.Range("A2:H7")


Howard

Option Explicit

Sub ReplaceInRange()

Dim rngBig As Range, c As Range
Dim myStr As String

 
myStr = Application.InputBox(prompt:="Enter the string to replace.", Title:="My Search", Type:=2)

With ActiveSheet.Range("A2:H7")
Set c = .Find(myStr, LookIn:=xlValues, lookat:=xlWhole)

   If Not c Is Nothing Then
     
      Do
         If rngBig Is Nothing Then
            Set rngBig = c
            c = Cells(c.Row, 1).Value
         Else
            c = Cells(c.Row, 1).Value
            Set rngBig = Union(rngBig, c)
            
         End If
         Set c = .FindNext(c)
      
     Loop While Not c Is Nothing
      
   End If
  
End With

End Sub