can someone explain this vba code for me ?

unityjon

New Member
Joined
Jan 12, 2005
Messages
45
I have some code in my project that searches a worksheet and displays the matches in a textbox but i cannot understand what it is looking at so that i can amend it to look elsewhere, the code looks like this:
Code:
'add the values to the listbox
            Do
                If rngFind.Row > 1 Then
                    lstLookup.AddItem rngFind.Value
                    lstLookup.List(lstLookup.ListCount - 1, 1) = rngFind.Offset(0, 1)
                    lstLookup.List(lstLookup.ListCount - 1, 2) = rngFind.Offset(0, 2)
                    lstLookup.List(lstLookup.ListCount - 1, 3) = rngFind.Offset(0, 3)
                    lstLookup.List(lstLookup.ListCount - 1, 4) = rngFind.Offset(0, 4)
                    lstLookup.List(lstLookup.ListCount - 1, 5) = rngFind.Offset(0, 5)
                    lstLookup.List(lstLookup.ListCount - 1, 6) = rngFind.Offset(0, 6)
                    lstLookup.List(lstLookup.ListCount - 1, 7) = rngFind.Offset(0, 7)

                End If
                'find the next address to add
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If

it works well and produces a list showing the contents of Column 1, Column 2, Column 3, Column 4, Column 5, Colum 6, Column 7

This is all good but i actually want to see the contents of different columns so i have tried changing the code for example to see column 20 i tried:
Code:
lstLookup.List(lstLookup.ListCount - 1, 7) = rngFind.Offset(0, 20)
but this failed nothing changed
I also tried :
Code:
lstLookup.List(lstLookup.ListCount - 1, 20) = rngFind.Offset(0, 7)
this also failed but with an error

what am i missing here, i thought i t was offsets from from rngFind as the code says therefore not column a,b,c,d etc ?

this is really puzzling me, what i'm asking for is a break down of what this line of code is saying (in english !)
Code:
lstLookup.List(lstLookup.ListCount - 1, 7) = rngFind.Offset(0, 7)
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Due to the fact that listbox indices start at 0, that line says:
Put the value from the cell 7 columns to the right of the found cell into the 8th column of the listbox.
Note: if you want column 20 on the sheet, that's an offset of 19 columns to the right from the found cell, not 20.
 

unityjon

New Member
Joined
Jan 12, 2005
Messages
45
many thanks, english certainly helps ! looks like i need to do some work on the column formatting in the textbox to get it spot on but much clearer now.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,695
Members
416,127
Latest member
MALEPINZON

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top