Is there something wrong with my code?

C

Corey

I am pulling data into a userform, then if needed it can be modified,
but if i make a modification i do NOT sem to get any data to change accordingly, why ?

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim rngFound As Range
On Error Resume Next

With Worksheets("Contact List").Range("A:A")
Set rngFound = .Find(What:=Me.ComboBox1.Value, After:=.Columns("A"), LookIn:=xlValues,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False)
If ListBox1.Value = rngFound.Offset(0, 1).Value Then
TextBox1.Value = rngFound.Offset(0, 10).Value
ComboBox2.Value = rngFound.Offset(0, 11).Value
TextBox2.Value = rngFound.Offset(0, 12).Value
TextBox3.Value = rngFound.Offset(0, 13).Value
TextBox4.Value = rngFound.Offset(0, 14).Value
TextBox5.Value = rngFound.Offset(0, 15).Value
TextBox6.Value = rngFound.Offset(0, 16).Value
TextBox7.Value = rngFound.Offset(0, 17).Value
ComboBox3.Value = rngFound.Offset(0, 18).Value
If CheckBox1 = True Then rngFound.Offset(0, 19).Value = "415v" Else rngFound.Offset(0, 19).Value
= ""
If CheckBox2 = True Then rngFound.Offset(0, 20).Value = "240v" Else rngFound.Offset(0, 20).Value
= ""
If CheckBox3 = True Then rngFound.Offset(0, 21).Value = "Other ...." Else rngFound.Offset(0,
21).Value = ""
If CheckBox4 = True Then rngFound.Offset(0, 22).Value = "GOOD" Else rngFound.Offset(0, 22).Value
= ""
If CheckBox5 = True Then rngFound.Offset(0, 23).Value = "FAIR" Else rngFound.Offset(0, 23).Value
= ""
If CheckBox6 = True Then rngFound.Offset(0, 24).Value = "POOR" Else rngFound.Offset(0, 24).Value
= ""
If CheckBox7 = True Then rngFound.Offset(0, 25).Value = "OTHER .." Else rngFound.Offset(0,
25).Value = ""
MsgBox "Done!"
Else
MsgBox "There is NO Record of that Site OR Contact Person ! ", vbCritical, " ...."
End If
End With
Unload Me
Application.ScreenUpdating = True
End Sub


Am i missing something ?

Corey....
 
A

Alan

Reporting the error code and what line occurs always helps. You should
develop a practice of insetting your code so that it is easier to
troubleshoot your End If or End With statements. It also make it much easier
for others who might work with your code. For every If Then statement, using
it as your are, you must have and End If statement, even if you use Else.
Don't place your Else statements on the same line. If the debugger breaks on
a line with If Then & Else, you may be unsure which portion of the code
failed. Just a few suggestions.

I am not sure if this is how you want your CheckBox code set up or not.
Depends whether you are nesting any of your If Thens or not. This is not.
Not tested.

Regards,

Alan


With Worksheets("Sheet").Range("A:A")
Set rngFound = .Find(What:=Me.ComboBox1.Value, After:=.Columns("A"),
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, Matchbyte:=False)
If ListBox1.Value = rngFound.Offset(0, 1).Value Then
TextBox1.Value = rngFound.Offset(0, 10).Value
ComboBox2.Value = rngFound.Offset(0, 11).Value
TextBox2.Value = rngFound.Offset(0, 12).Value
TextBox3.Value = rngFound.Offset(0, 13).Value
TextBox4.Value = rngFound.Offset(0, 14).Value
TextBox5.Value = rngFound.Offset(0, 15).Value
TextBox6.Value = rngFound.Offset(0, 16).Value
TextBox7.Value = rngFound.Offset(0, 17).Value
ComboBox3.Value = rngFound.Offset(0, 18).Value
If CheckBox1 = True Then
rngFound.Offset(0, 19).Value = "415v"
Else
rngFound.Offset(0, 19).Value = ""
End If
If CheckBox2 = True Then
rngFound.Offset(0, 20).Value = "240v"
Else
rngFound.Offset(0, 20).Value = ""
End If
If CheckBox3 = True Then
rngFound.Offset(0, 21).Value = "Other ...."
Else
rngFound.Offset(0, 21).Value = ""
End If
If CheckBox4 = True Then
rngFound.Offset(0, 22).Value = "GOOD"
Else
rngFound.Offset(0, 22).Value = ""
End If
If CheckBox5 = True Then
rngFound.Offset(0, 23).Value = "FAIR"
Else
rngFound.Offset(0, 23).Value = ""
End If
If CheckBox6 = True Then
rngFound.Offset(0, 24).Value = "POOR"
Else
rngFound.Offset(0, 24).Value = ""
End If
If CheckBox7 = True Then
rngFound.Offset(0, 25).Value = "OTHER .."
Else
rngFound.Offset(0, 25).Value = ""
End If
MsgBox "Done!"
Else
MsgBox "There is NO Record of that Site OR Contact Person ! ",
vbCritical, " ...."
End If
End With
Unload Me
Application.ScreenUpdating = True
End Sub

Regards,

Alan
 
C

Corey

Thanks for the reply Alan,
I Copied and Pasted you code, but i diod not seem to get the data UPDAING either.
I then removed the 'On error resume next'
and then i get an error here :
Set rngFound = .Find(What:=Me.ComboBox1.Value, After:=.Columns("A"), LookIn:=xlValues,
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False)
Error is : TYPE MISMATCH

??


Corey

Reporting the error code and what line occurs always helps. You should
develop a practice of insetting your code so that it is easier to
troubleshoot your End If or End With statements. It also make it much easier
for others who might work with your code. For every If Then statement, using
it as your are, you must have and End If statement, even if you use Else.
Don't place your Else statements on the same line. If the debugger breaks on
a line with If Then & Else, you may be unsure which portion of the code
failed. Just a few suggestions.

I am not sure if this is how you want your CheckBox code set up or not.
Depends whether you are nesting any of your If Thens or not. This is not.
Not tested.

Regards,

Alan


With Worksheets("Sheet").Range("A:A")
Set rngFound = .Find(What:=Me.ComboBox1.Value, After:=.Columns("A"),
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, Matchbyte:=False)
If ListBox1.Value = rngFound.Offset(0, 1).Value Then
TextBox1.Value = rngFound.Offset(0, 10).Value
ComboBox2.Value = rngFound.Offset(0, 11).Value
TextBox2.Value = rngFound.Offset(0, 12).Value
TextBox3.Value = rngFound.Offset(0, 13).Value
TextBox4.Value = rngFound.Offset(0, 14).Value
TextBox5.Value = rngFound.Offset(0, 15).Value
TextBox6.Value = rngFound.Offset(0, 16).Value
TextBox7.Value = rngFound.Offset(0, 17).Value
ComboBox3.Value = rngFound.Offset(0, 18).Value
If CheckBox1 = True Then
rngFound.Offset(0, 19).Value = "415v"
Else
rngFound.Offset(0, 19).Value = ""
End If
If CheckBox2 = True Then
rngFound.Offset(0, 20).Value = "240v"
Else
rngFound.Offset(0, 20).Value = ""
End If
If CheckBox3 = True Then
rngFound.Offset(0, 21).Value = "Other ...."
Else
rngFound.Offset(0, 21).Value = ""
End If
If CheckBox4 = True Then
rngFound.Offset(0, 22).Value = "GOOD"
Else
rngFound.Offset(0, 22).Value = ""
End If
If CheckBox5 = True Then
rngFound.Offset(0, 23).Value = "FAIR"
Else
rngFound.Offset(0, 23).Value = ""
End If
If CheckBox6 = True Then
rngFound.Offset(0, 24).Value = "POOR"
Else
rngFound.Offset(0, 24).Value = ""
End If
If CheckBox7 = True Then
rngFound.Offset(0, 25).Value = "OTHER .."
Else
rngFound.Offset(0, 25).Value = ""
End If
MsgBox "Done!"
Else
MsgBox "There is NO Record of that Site OR Contact Person ! ",
vbCritical, " ...."
End If
End With
Unload Me
Application.ScreenUpdating = True
End Sub

Regards,

Alan
 
T

Tom Ogilvy

cells and range are the same type (range). Find is a method of range,
therefore it is also a method of cells.
 
D

Dave Peterson

I'd drop the "on error resume next" line and use something like:



With Worksheets("Contact List").Range("A:A")
Set rngFound = .Find(What:=Me.ComboBox1.Value, After:=.Cells(1), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False,
Matchbyte:=False)

if rngfound is nothing then
'not found, error message back to user????
else
'do the real work here
end if
....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

rngFound not finding correct value when more than one match 0
My on-going problems with dates 3
Stop TextBox Clearing Cell 3
Update existing data or Insert new problem 3
Code does not seem to work, but it should. 2
Assistance required 4
Re : Date to be placed into Column "C" with matching value in Column "B" in same row 1
Matching a Month Year value 0

Top