ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Is there something wrong with my code? (https://www.excelbanter.com/excel-programming/383547-re-there-something-wrong-my-code.html)

Corey
Is there something wrong with my code?
 
Still don't seem to get and data updating from the below code.

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:=.Cells(1), _
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
TextBox8.Value = rngFound.Offset(0, 26).Value ' When placing a value in Texbox8 NO VALUE
is Placed in the WorkSheet ??

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

I have adapted it according to others recomendation, to No avail thus afr.

Corey....



wbntravis
Is there something wrong with my code?
 
Enlight us

what the code is expected to do..



"Corey" wrote:

Still don't seem to get and data updating from the below code.

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:=.Cells(1), _
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
TextBox8.Value = rngFound.Offset(0, 26).Value ' When placing a value in Texbox8 NO VALUE
is Placed in the WorkSheet ??

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

I have adapted it according to others recomendation, to No avail thus afr.

Corey....




Dave Peterson
Is there something wrong with my code?
 
Could it be that the value wasn't found?



Corey wrote:

Still don't seem to get and data updating from the below code.

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:=.Cells(1), _
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
TextBox8.Value = rngFound.Offset(0, 26).Value ' When placing a value in Texbox8 NO VALUE
is Placed in the WorkSheet ??

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

I have adapted it according to others recomendation, to No avail thus afr.

Corey....

--

Dave Peterson


All times are GMT +1. The time now is 09:22 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com