Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Is there something wrong with my code?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 363
Default Is there something wrong with my code?
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....


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 116
Default Is there something wrong with my code?
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


"Corey" wrote in message
...
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....




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 363
Default Is there something wrong with my code?
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

"Alan" wrote in message ...
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


"Corey" wrote in message
...
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....





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,092
Default Is there something wrong with my code?
Corey, I believe that .Find is a method of Cells, not Range.

Mike F
"Corey" wrote in message
...
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....




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Is there something wrong with my code?
cells and range are the same type (range). Find is a method of range,
therefore it is also a method of cells.

--
Regards,
Tom Ogilvy


"Mike Fogleman" wrote:

Corey, I believe that .Find is a method of Cells, not Range.

Mike F
"Corey" wrote in message
...
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....





Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Is there something wrong with my code?
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
....

Corey wrote:

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....

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
What is wrong with the code? Eric Excel Discussion (Misc queries) 2 September 13th 07 10:36 AM
Can someone tell me what is wrong with this code? Ant Excel Discussion (Misc queries) 8 November 14th 05 02:53 PM
HELP! What's wrong with my code? Sethaholic[_6_] Excel Programming 0 July 11th 05 07:37 PM
What is wrong with this code? Jan Excel Programming 4 June 14th 05 06:48 PM
Is something wrong with the code Patrick Molloy[_3_] Excel Programming 1 July 15th 03 08:28 AM


All times are GMT +1. The time now is 04:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement