VBA Help for a learner!

[Sazzle14]

HI sorry I am new to VBA and this is only from me trying to self teach.
What I am trying to do is, for each column where there is an "X" replace that "X" with the column title.

Problem is I am struggling to:

a) get the code to do this for each column
b) this table is just an example the actual data is much longer so I don't want to specify how many rows.

Before code
Column1 z_SAP_A z_SAP_B Z_SAP_C
assistant X x
buyer X X
supply X x
manager x X


what I would like after code
Column1 z_SAP_A z_SAP_B Z_SAP_C
assistant z_SAP_A x
buyer z_SAP_B Z_SAP_C
supply z_SAP_A x
manager x Z_SAP_C


Currently what I have
Private Sub CommandButton1_Click()
Dim code As Variant, x As Variant, z As Integer
Dim i As Integer, a As Integer
code = "X"
z = 2
x = Cells(2, z).Value
a = 3
Do Until x = ""
For a = a To 7
For i = 1 To 7
If Cells(a, i).Value = code Then
Cells(a, i) = x
z = z + 1
End If
Next i
Next a
Loop
End Sub

I hope I have given you enough - apologies for my lack of knowledge on this!

thanks

 

[Smitty]

Welcome to the Board!

Why not just use Find and Replace? It's easily recordable if you need to repeat it.

HTH,

 

[MrKowz]

Welcome to the forums!

Try:

Public Sub ReplaceX()
Dim rng     As Range, _
    rng1    As String, _
    LC      As Long, _
    LR      As Long, _
    code    As String
    
code = "X"
LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
With Range(Cells(1, 1), Cells(LR, LC))
    Set rng = .Find(code, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
    If Not rng Is Nothing Then
        rng1 = rng.Address
        Do
            rng.Value = Cells(1, rng.Column)
            Set rng = .FindNext(rng)
        Loop While Not rng Is Nothing And rng.Address <> rng1
    End If
End With
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[Glory]

I had no idea you could do that... Set inside with.

Nice.

 

[Sazzle14]

Hello MrKowz

Thanks for your post, I am trying the code you have sent. However when I click the button - nothing is happening.

Sorry I must be doing something wrong but don't know what that something is!!

Sarah

 

[Glory]

Have you changed the name of your button?

 

[MrKowz]

Note that the find I am using in this is caSE SenSItIvE. Also, make sure that you have data extending to the bottom of your table in column A, and data extending to the end of your columns in row 1.

 

[Sazzle14]

Hi Glory

I have now to match the code - but now I get a debug error at

Loop While Not rng Is Nothing And rng.Address <> rng1

x

 

[MrKowz]

Try adjusting the code to:

Public Sub ReplaceX()
Dim rng     As Range, _
    rng1    As String, _
    LC      As Long, _
    LR      As Long, _
    code    As String
    
code = "X"
LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
With Range(Cells(1, 1), Cells(LR, LC))
    Set rng = .Find(code, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
    If Not rng Is Nothing Then
        rng1 = rng.Address
        Do
            rng.Value = Cells(1, rng.Column)
            Set rng = .FindNext(rng)
        Loop While Not rng Is Nothing
    End If
End With
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

I've ran into issues when altering the actual find values. This should fix that.

 

[Sazzle14]

ah the problem is that some cells will be blank

sorry the table i pasted didn't show that very well