Thread: Macro help - finding and displaying cell values

In this code

    width1 = Cells(rowV1, 6).Value
    width2 = Cells(rowT1, 13).Value

you are getting the value from column 6, which is F. That's GVWR, not width. Then you get a value from column 13, which is M, which is blank.

You also seem to confusing the two worksheets in your code. Instead of activating a sheet, use explicit qualification for each cell reference.

Also, you are using a MATCH function to look up the position of the selected item, but the ListIndex property of the list already tells you that. ListIndex starts using 0 as the first item so you have to add 1 to get the row number. Also you have a header row, so you have to add 1 again.

You also use intermediate variables where they aren't really necessary. This sometimes make code more readable, but sometimes just makes it bigger.

You seem to be loading the second listbox from the list of trailer GVWR. I think you meant to use the trailer name.

One suggestion, which I didn't implement: You may want to create named ranges for the vehicle and trailer names. Then if you change the way your data is organized, you won't have to change the listbox range source.

Here is how I would write this code, book is also attached:

Private Sub CommandButton1_Click()
    
    With Worksheets("Macro")
    
      ' Vehicle
      .Cells(7, 1).Value = ListBox1.Value
      .Cells(7, 2).Value = Worksheets("List").Cells(ListBox1.ListIndex + 2, 5).Value ' width
      .Cells(7, 3).Value = Worksheets("List").Cells(ListBox1.ListIndex + 2, 2).Value ' length
      
      ' Trailer
      .Cells(8, 1).Value = ListBox2.Value
      .Cells(8, 2).Value = Worksheets("List").Cells(ListBox2.ListIndex + 2, 11).Value ' width
      .Cells(8, 3).Value = Worksheets("List").Cells(ListBox2.ListIndex + 2, 10).Value ' length
   
    End With
    
End Sub

That works great. Thank you so much!

I could not get the way you suggested to work given my code, but the following code allows me to do what I want.

With each click in the userform, the counter and all the information in the click is moved to another sheet called history, and since the information that is copied occupies 2 rows, i want it to skip a row and then continue onto the one after that. Each click stores history based on the number.

The code that I have messes up on the first two clicks. The first click puts the items that were copied in the first spot I want, but then the second click overwrites the first clicks copy. The third click skips a line and copies the information correctly. I do not understand why that is. My code is below:

This code is for the counter...

Num = Sheets("Macro").Cells(7, 1).Value 'A1 change to suit
        If Sheets("Macro").Cells(7, 1).Value = "" Then
            Num = 1
            Sheets("Macro").Cells(7, 1).Value = Num
        Else: Sheets("Macro").Cells(7, 1).Value = Num + 1
        End If

This code is for the copy command.

 Set srang1 = Range(Cells(7, xlFirstCol), (Cells(xlLastRow, 2)))
        Set srang2 = Range(Cells(7, xlFirstCol + 1), (Cells(xlLastRow, xlFirstCol + 1)))
        Set srang3 = Range(Cells(7, xlFirstCol + 2), (Cells(xlLastRow, xlFirstCol + 2)))
        Set srang4 = Range(Cells(7, xlFirstCol + 3), (Cells(xlLastRow, xlFirstCol + 3)))
        Set srang5 = Range(Cells(7, xlFirstCol + 4), (Cells(xlLastRow, xlFirstCol + 4)))
        Set srang6 = Range(Cells(7, xlFirstCol + 5), (Cells(xlLastRow, xlFirstCol + 5)))
        Set srang7 = Range(Cells(7, xlFirstCol + 6), (Cells(xlLastRow, xlFirstCol + 6)))
        Set MyRange = Union(srang1, srang2, srang3, srang4, srang5, srang6, srang7)
        If Num = 1 Then
        MyRange.Copy Destination:=Sheets("Hist").Cells(3, 1)
        ElseIf Num > 1 Then
        MyRange.Copy Destination:=Sheets("Hist").Cells(CurPos(Num), 1)
        End If
Function xlFirstCol(Optional WorksheetName As String) As Long
     
     '  find the first populated column in a worksheet
     
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    With Worksheets(WorksheetName)
        xlFirstCol = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByColumns, xlNext).Column
    End With

End Function

Function xlFirstRow(Optional WorksheetName As String) As Long
     
     '  find the first populated row in a worksheet
     
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    With Worksheets(WorksheetName)
        xlFirstRow = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlNext).row
    End With
     
End Function

Function xlLastRow(Optional WorksheetName As String) As Long
     
     '    find the last populated row in a worksheet
     
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    With Worksheets(WorksheetName)
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlPrevious).row
    End With
     
End Function
Function xlLastCol(Optional WorksheetName As String) As Long

     '    find the last populated column in a worksheet
     
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    With Worksheets(WorksheetName)
        xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByColumns, xlPrevious).Column
    End With
End Function

Function CurPos(Num)
CurPos = Num * 3
End Function

Any suggestions would be appreciated!

With a problem like this you need to attach your whole workbook. I can't see what happens when you click a button.

I could not get the way you suggested to work given my code....

I am very puzzled by this seeing as though I didn't just make a suggestion. I took your workbook, fixed it, and then posted the completed solution. What did you post if it wasn't your code?

I apologize, I posted this is in the wrong thread. I meant to post it in my other thread.