lining up information across columns

[cwp_hks]

I am using Excel. I am trying to compare information within two columns to each other and line the second columns rows up with the first to where the information matches. In the long run, I want to be able to move multiple columns of the same row to line up with one column. (I'm matching PLC addresses to all the different descriptions of each address by finding which row has the corresponding PLC address with it). But as of right now, I keep getting error 91, something about With block variable not set. The help file nor google helped me with this error, so could someone take a look at my code and explain it to me please? Thank you.

Sub LineUp()
Dim i As Integer
Dim h As Integer
Dim worksheet As Worksheet
i = 1
h = 1
With worksheet
For h = 1 To 10 Step 1
If worksheet.Range("Ai").Value = worksheet.Range("Dh, D10").Value Then
worksheet.Range("F1") = worksheet.Range("D1").Value
End If
Next h
End With
End Sub


P.S. i isn't being used yet

[sparbag]

Welcome to the forums

with block variable not set (error91)
This is caused by the worksheet object not being assigned to an object.

Set worksheet = WorkSheets(1)

The most efficient method of completing the task you have described would be to use VLookup functions, whether in excel cells of in code.

Let us assume column A has all of our values that are our PLC addresses. Column F has a very long list of plc addresses.

First action in code is to check whether there is a matching plc value in out long list contained in column F

Sub lineup()
    Dim r As Integer ' row counter
    Dim sht As worksheet
    Dim dataRange As Range
    Set dataRange = Range("F:F")
    r = 2 'to allow for column headings
    Set sht = Worksheets(1)  'first sheet in workbook
    Do While sht.Cells(r, 1).Value <> ""
        If WorksheetFunction.CountA(dataRange, sht.Cells(r, 1).Value) > 0 Then  'we have a matching value in the target column
            sht.Cells(r, 2) = WorksheetFunction.VLookup(sht.Cells(r, 1).Value, dataRange, 1, False)
        End If
        r = r + 1
    Loop
    Set dataRange = Nothing
    Set sht = Nothing
End Sub

Code is untested.

If this was me trying to complete this task I would use VLookup in excel cells and pull down through the range manually using the formula
=IFERROR(VLOOKUP($A2, $F$F, 1, FALSE), "")
assuming the target data was contained in column F.


Kind regards

Steve

[cwp_hks]

Awesome! That works great for one column! Now, I think if you could just tell me which line of the code makes the second column match the first I could manipulate it to effect multiple columns. I need to keep all the information in the same row, other than the test column, together. I have 40 some odd columns of information, and each row pertains to a certain PLC address, so I need to keep all the information that relates to the same PLC address on the same row. Is that possible? I would think so. Cutting and pasting 1500+ addresses worth is getting old. Thank you for your help!

[cwp_hks]

Oh wow. So I just tried entering numbers that aren't in the initial test column, and it deleted those and put what should be there. I don't want that, I want it to move information that is already there to where it should be. It shouldn't be creating anything.

[sparbag]

Hello

Welcome to the world of vlookup.

You need to understand how vlookup works:
VLookup(Value to lookup (PLC for instance), the range or table the value may be found in, the column number across you need to locate the value, false means there will be no duplicates in the search column and true means that there will be duplicates in the search column.)

If it is any consolation 40 + columns can be achieved by the third attribute of the function to match the column you want to search.
Data structure will be the key to designing a suitable solution.

Kind regards

Steve