vba code to replace the column names for those column name "Proposal that starts with "1."

[mvneema]

I am in need of help to build up a macro that will replace the column names at row 4. I have an excel sheet that looks like the sheet1 in the attached. I need to replace the column names for those column name "Proposal#" that has values 1 and 1.* that starts from row 5. It has to loop columnwise and check for each proposal# and if the value is 1 or 1.* then it should replace. The desired output is in the Sheet2. Can anyone please help me on this.

[sktneer]

Try this code to see if this helps........

Sub ChangeColName()
Dim rng As Range, cell As Range
Dim lc As Integer
lc = Cells(4, Columns.Count).End(xlToLeft).Column
Set rng = Range(Cells(4, 1), Cells(4, lc))
For Each cell In rng
    cell.Select
    On Error Resume Next
    If WorksheetFunction.Find("Proposal", cell) > 0 Then
    If Err > 1 Then
    'do nothing
    Else
    cell = "First " & cell
    End If
    End If
Next cell
End Sub

[alansidman]

Try this:

Option Explicit

Sub Cols()
    Dim lc As Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim i As Long
    
    For i = 1 To lc
    If Cells(4, i) = "Proposal#" And Cells(5, i) Like "1*" Then
    Cells(4, i) = "First Proposal#"
    End If
    Next i

End Sub

[mvneema]

Hi sktneer, i tried your code but it replace all the Proposal# to First Proposal. My requirement was to replace the Proposal# and Votecast for those values with 1.*
Hi alansidman, i tried your code but it didnot replace the Votecast to First Vote Cast but it did replace Proposal# except the Range Q:S

Can you help me on this?

[alansidman]

Try this:

Option Explicit

Sub Cols()
    Dim lc As Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim i As Long
    
    For i = 1 To lc
    If Cells(4, i) = "Proposal#" And Cells(5, i) Like "1*" Then
    Cells(4, i) = "First Proposal#"
    If Cells(4, i) = "Vote Cast" And Cells(5, 1) Like "1*" Then
    Cells(4, i) = "First Vote Cast"
    End If
    
    End If
    Next i

End Sub

It does not change Columns Q:S because in line 5 they are blank. Your criteria in the first post references row 5 as the source of 1.*

[mvneema]

Ok alansidman, but I need those Proposal# and Vote Cast from Merged Cell "Current Year" to be replaced by First Proposal# and First Vote Cast for which the values is 1 or 1.*. It has to change the columns Q:S even if the line 5 are blank since I would get data that will be blank sometimes. I have attached an updated excel sheet. Your code did not update the Vote Cast. Can you help me?

[mvneema]

Can anyone help me on this?

[alansidman]

Your lateste explanation is confusing. In your new example I do not see a first vote cast. How about explaining what specifically you want and don't want and forget about me chasing down 50+ columns of data trying to figure it out. Be specific as to what you want changed and what criteria would change it.

[alansidman]

Ok. I see that you have solved it. Good. Been away for the morning.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[mvneema]

Hi, alansidman. I just modified your code but considered only those cells that has 1.* in it and selecting its corresponding offset(0,2) and changing it to "First Vote Cast" But I am still not able to change the columns for the range (Q:S) since its values for the row is blank. Can you provide some help in this.

Sub Cols1()
    Dim lc As Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim i, j As Long
    Dim rng As Range
    For j = Range("D" & Rows.Count).End(3)(1).Row To 4 Step -1
    For i = 1 To lc
    If Cells(4, i) = "Proposal#" And Cells(5, i) Like "1*" Then
    Cells(4, i) = "First Proposal#"
    Set rng = Range(Cells(4, i).Address).Offset(0, 2)
    MsgBox Cells(j, i).Value
    'MsgBox rng
    rng.Value = "First Vote Cast"
        
    End If
    Next i
    Next j
End Sub

[mvneema]

I figured it out myself
here is the below code that worked.

Sub Cols1()
    Dim lc As Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim i, j As Long
    Dim rng As Range
    For j = Range("D" & Rows.Count).End(3)(1).Row To 4 Step -1
    For i = 1 To lc
    If Cells(4, i) = "Proposal#" And Cells(j, i) Like "1.*" Or Cells(j, i) Like "1" Then
    Cells(4, i) = "First Proposal#"
    Set rng = Range(Cells(4, i).Address).Offset(0, 2)
    MsgBox Cells(j, i).Value
    'MsgBox rng
    rng.Value = "First Vote Cast"
    'MsgBox Range(Cells(4, i).Address).Offset(0, 2)
    'If Cells(4, i) = "Vote Cast" And Cells(5, 1) Like "1*" Then
    'Cells(4, i) = "First Vote Cast"
  
    
    End If
    Next i
    Next j
End Sub