Userform Help Please

[Topher]

Hope you guys can help, whilst I try I have to admit to not being very good
at this...

I have a sheet with rows of data. The unique identifier (UI) is in column
B. By using a userform I would like to insert data in in the row belonging
to the UI.

On the useform, as the UI exisits I assume I need to use a combo box so the
user can select the UI. I then want them to enter a number of answers in
boxes to provide the data, and for this I can use text box's? So far so
good. What I seem to be missing is how do I get the data entered to go to
the correct row and cells?

Thanks in advance

 

[Dave Peterson]

Since it's a unique ID, you can use Excel's =Match() to find the correct row.

Dim res as variant
dim UIDRng as range
dim UIDWks as worksheet

set uidwks = worksheets("sheetwithuniqueidinit")

with wks
set uidrng = .range("B:b")
end with


'not sure if you can have nothing in your combobox
if me.combobox1.value = "" then
beep
exit sub
end if

'check for a text match (that's what the combobox holds
res = application.match(me.combobox1.value, uidrng,0)
if iserror(res) then
'no match, check for a match for numbers
if isnumeric(me.combobox1.value) then
res = application.match(val(me.combobox1.value), uidrng, 0)
end if
end if

'is it still an error
if iserror(res) then
'no match for either!
msgbox "no match" 'Should this happen???
exit sub
end if

uidwks.cells(res,"A").value = "this is the value that goes in column A"
uidwks.cells(res,"Z").value = "New value in column Z
....

All untested, uncompiled. Watch for typos!

 

[Topher]

Dave, thanks for your time to help but it does not seem to find the 'match'
as it places the data in the right column but not the right row. Could be me
though, my code is:

Private Sub Enter_Click()


Dim JOBName As Variant
Dim UIDRng As Range
Dim UIDWks As Worksheet

Set UIDWks = Worksheets("Ops Schedule")

With wks
Set UIDRng = Range("ABC")
End With


'not sure if you can have nothing in your combobox
If Me.Job_List1.Value = "" Then
Beep
Exit Sub
End If

'check for a text match (that's what the combobox holds
JOBName = Application.Match(Me.Job_List1.Value, UIDRng, 0)
If IsError(JOBName) Then
'no match, check for a match for numbers
If IsNumeric(Me.Job_List1.Value) Then
JOBName = Application.Match(Val(Me.Job_List1.Value), UIDRng, 0)
End If
End If

'is it still an error
If IsError(JOBName) Then
'no match for either!
MsgBox "no match" 'Should this happen???
Exit Sub
End If

Call Unprotect 'unprotects the sheet

UIDWks.Cells(JOBName, "FC").Value = IF_Qty.text


Call Protect 'Protects the sheet

End Sub



Private Sub UserForm_Activate()


Call Project_Name_Range 'Names the range "ABC"
Call Sort_Project_Alfa 'Does an alfabetic sort

End Sub

 

[Dave Peterson]

I used the entire column so that when there's a match, res refers to the row
with the matching value.

But you used:

With wks
Set UIDRng = Range("ABC")
End With

First, you'll want to qualify the range
With wks
Set UIDRng = .Range("ABC")
End With

The dot means that this range object refers to the object in the previous with
statement -- in this case, it's the wks worksheet.

And you could either change the code that adds in that starting row of the
..Range("ABC") range, but I'd try:

With wks
Set UIDRng = .Range("ABC").entirecolumn.columns(1)
End With

 

[Topher]

Thanks Dave you are a star!

I used the entire column so that when there's a match, res refers to the row
with the matching value.

But you used:

With wks
Set UIDRng = Range("ABC")
End With

First, you'll want to qualify the range
With wks
Set UIDRng = .Range("ABC")
End With

The dot means that this range object refers to the object in the previous with
statement -- in this case, it's the wks worksheet.

And you could either change the code that adds in that starting row of the
..Range("ABC") range, but I'd try:

With wks
Set UIDRng = .Range("ABC").entirecolumn.columns(1)
End With







.