How to code using the offset(x, x) with this ?

Hi All,
Userforms
Userform1 = Data that inputs into InspectionData Sheet
Userform3 = Prompt to Ask for a Certain Value in InspectionData.Column A

Sheets
InspectionData = values from a userform are stored here.
Inspections = Sheet to be printed out(either BLANK or with Values pulled from InspectionData sheet)



Code to fill Combobox1 on userform3
Private Sub UserForm_Initialize()

Dim lastcell As Long
Dim myrow As Long

lastcell = workSheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row

With ActiveWorkbook.workSheets("InspectionData")
.Select 'first thing to do with a With statement that occurs on a second sheet
For myrow = 2 To lastcell
If .Cells(myrow, 1) <> "" Then
If IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then 'this part excludes the # sign then checks for numeric
ComboBox1.AddItem Cells(myrow, 1)
End If
End If

Next
End With
End Sub




Code to Place data into another sheet
(When OK button is clicked on userform3)

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False

UserForm3.Hide
Unload Me

With ActiveWorkbook.workSheets("Inspections")
.Select
Range("O4").Value = ComboBox1.Value ' Value that was selected from Userform3.

ActiveWindow.SelectedSheets.PrintPreview
Range("O4").ClearContents
Sheets("Main").Select
Range("A1").Select
Application.ScreenUpdating = True
End With
End Sub

I need to be able to get many other values from the InspectionData sheet, BUT I need to reference these other values from the Value that was selected in the combobox.

EG. The value selected in the combobox could actually be:
sheets("InspectionData").Range("A125")
The next value i need to get is in cell:
sheets("InspectionData").Range("C124") IE. offset(2,-1) from the combobox cell.

I am not fluent enough in VBA to set this up.

Can someone please assist me to code the combobox value as a TARGET or ADDRESS, or the like, and then how i can code a cell.offset from there?

Regards

Corey....

Originally Posted by coreytroy

I need to be able to get many other values from the InspectionData sheet, BUT I need to reference these other values from the Value that was selected in the combobox.

If I've understood correctly, what you could do here is when you add an item to the ComboBox, also store the row number of the item in a Collection.

Then when the user selects the item in the ComboBox, use the ListIndex property as an index into the Collection to return the row number of the item.

Please Login or Register  to view this content.

Thank you for the reply T-J.
This is what i ended up with, but i am getting the ROW Number (2) placed in the E2 cell.

I have an explanation of what SHOULD be placed in range(E2) below.

~~~~~~~~~~~~~~~~~~~~~~~~
Dim ComboItemRow As New Collection

Private Sub CommandButton1_Click()
UserForm3.Hide
With ActiveWorkbook.workSheets("Inspections")
.Select
Range("O4").Value = ComboBox1.Value ' Value that was selected from Userform3.
Range("E2").Value = ComboItemRow.Item(ComboBox1.ListIndex + 1) ' <==== This is where i need the value in the cell offset(-1, 2) from where the combobox1. value came from....
End With
ActiveWindow.SelectedSheets.PrintPreview ' Preview worksheet to either Print of simply View
'Range("O4,E2").ClearContents ' Clears all data that was brought in from sheet "InspectionData"
Sheets("Main").Select ' Takes the user back to the Main Page
Range("A1").Select
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim lastcell As Long
Dim myrow As Long

lastcell = workSheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWorkbook.workSheets("InspectionData")
.Select 'first thing to do with a With statement that occurs on a second sheet
For myrow = 2 To lastcell
If .Cells(myrow, 1) <> "" Then
If IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) Then 'this part excludes the # sign then checks for numeric
ComboBox1.AddItem Cells(myrow, 1)
ComboItemRow.Add Item:=myrow 'add row number of item to collection
End If
End If
Next
End With
End Sub

~~~~~~~~~~~~~~~~~~~~~~~

Not sure if you missunderstood my post, or i am stuffin gup something here.

Corey....

Originally Posted by coreytroy

Thank you for the reply T-J.
This is what i ended up with, but i am getting the ROW Number (2) placed in the E2 cell.

I have an explanation of what SHOULD be placed in range(E2) below.

Please Login or Register  to view this content.

Not sure if you missunderstood my post, or i am stuffin gup something here.

Corey....

Yes I think I've got it now, just a small adjustment is required:

That line above becomes

Please Login or Register  to view this content.

As the selected ComboBox item comes from the InspectionData sheet, you have to reference that sheet to get a value from it.

Offset(-1, 2).Value will return the value (1 row above and 2 columns to the right) of the selected ComboBox item.