Confused: Same code - different results

Big Lar

Well-known Member
Joined
May 19, 2002
Messages
552
I’m attempting to assemble a ws with several columns of data.
Macro1 works just fine.
Except for the column & iRow cell references, Macro2 appears to me to be the same code. However, it replaces the existing data.
Any ideas on how can this be resolved?

Rich (BB code):
SubMacro1()
Dim FullName As String
    Dim iRow As Long
    Dim ws As Worksheet

Set ws =Worksheets("Teams")
ws.Select
ws.Unprotect

    FullName = UserFormPostScores.ComboBox1

    With ws
        If UserFormPostScores.TextBox85.Value =1 Then

    FullName =UserFormPostScores.ComboBox1.Value
    Set CLoc =ws.Columns("B:B").Find(What:=FullName, after:=ws.Cells(1, 2),LookIn:= _
                            xlFormulas,LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
                            xlNext,MatchCase:=False, SearchFormat:=False)

    If CLoc Is Nothing Then
        iRow = ws.Cells(Rows.Count, 1) _
               .End(xlUp).Offset(1, 0).Row
    Else

        iRow = CLoc.Row
    End If

    ws.Cells(iRow, 1).Formula ="=Rand()"
    ws.Cells(iRow, 2).Value =UserFormPostScores.ComboBox1
    ws.Cells(iRow, 3).Value =UserFormPostScores.TextBox90
    ws.Cells(iRow, 4).Value =UserFormPostScores.TextBox36.Value
    ws.Cells(iRow, 5).Value =UserFormPostScores.TextBox24.Value - UserFormPostScores.TextBox3.Value
Else
 End If
  End With

CallMacroBuildTeams2
End Sub
__________________________________

Sub Macro2()

Dim FullNameAs String
    Dim iRow As Long
    Dim ws As Worksheet

Set ws =Worksheets("Teams")
ws.Select
ws.Unprotect

    FullName = UserFormPostScores.ComboBox1

    With ws
        If UserFormPostScores.TextBox85.Value =2 Then

    FullName =UserFormPostScores.ComboBox1.Value

    Set CLoc =ws.Columns("G:G").Find(What:=FullName, after:=ws.Cells(1, 7),LookIn:= _
                            xlFormulas, LookAt:=xlWhole,SearchOrder:=xlByColumns, SearchDirection:= _
                            xlNext,MatchCase:=False, SearchFormat:=False)

    If CLoc Is Nothing Then
        iRow = ws.Cells(Rows.Count, 1) _
               .End(xlUp).Offset(1, 0).Row
    Else

        iRow = CLoc.Row
    End If

    ws.Cells(iRow, 6).Formula ="=Rand()"
    ws.Cells(iRow, 7).Value =UserFormPostScores.ComboBox1
    ws.Cells(iRow, 8).Value =UserFormPostScores.TextBox90
    ws.Cells(iRow, 9).Value =UserFormPostScores.TextBox36.Value
    ws.Cells(iRow, 10).Value =UserFormPostScores.TextBox24.Value - UserFormPostScores.TextBox3.Value
Else
 End If
  End With
End Sub

 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,042
Office Version
365
Platform
Windows
What is the value of CLoc.Row when it writes to the wrong row?
 

Big Lar

Well-known Member
Joined
May 19, 2002
Messages
552
CLoc.Row values are always correct.
Only Macro1 works correctly:
It starts at cell[A2] inputting the random number formula,
[B2]=
ComboBox1, [C2]=Textbox90, [D2]=Textbox36, etc.
Added values fall in place correctly, AND, duplicate ComboBox1 values are prevented.

Macro2 puts all data in the correct columns but only on Row3, AND, subsequent entries replace the existing values. It's a mystery...to me!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,042
Office Version
365
Platform
Windows
Macro2 puts all data in the correct columns but only on Row3, AND, subsequent entries replace the existing values. It's a mystery...to me!
That's why I was asking what the value of CLoc.Row was. It sounds as though the "FullName" value is only being found on row3 & hence being overwritten
 

Watch MrExcel Video

Forum statistics

Threads
1,099,696
Messages
5,470,221
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top