ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help with type mismatch error (https://www.excelbanter.com/excel-programming/392943-help-type-mismatch-error.html)

[email protected]
Help with type mismatch error
 
Hi all,

I've posted about this problem later last week, but I still haven't
been able to fix up my code. Thanks for all the help, I'm really not
too great figuring out the errors.

The issue is going to be in the Set c = Range... line up until the End
If line. All of the other code has previously worked until I added
this new part. Basically the code is trying to copy and paste certain
data from one spreadsheet to another in a specific location. This one
part is trying to find the line in the other spreadsheet where it
should copy - depending a match within the G column. If it doesn't
find a match, it isn't supposed to copy anywhere. I'm currently
getting a type mismatch error on the Set c = Range... line. I really
don't know VB syntax at all, so I'm not sure what's wrong. Thanks!


Selection.Copy
x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
If Not c Is Nothing Then
c.Activate
Else
Exit Sub
End If
Windows("May as of 06-01-07.xls").Activate
datecomp = "A1"
cellshow = "B" & ActiveCell.Row
cellSTB = "H" & ActiveCell.Row
cellorder = "I" & ActiveCell.Row
testdate = "D" & ActiveCell.Row
Worksheets("Sheet5").Range(cellshow).Font.Italic = True
Worksheets("Sheet5").Range(cellshow).Font.Bold = True
Range(cellSTB).Copy
If (Range(testdate) Range(datecomp)) Then
Windows("VOD Master List as of 06-19-07.xls").Activate
cellSTBpaste = "Y" & ActiveCell.Row
cellorderpaste = "X" & ActiveCell.Row
Else
Windows("VOD Master List as of 06-19-07.xls").Activate
cellSTBpaste = "AB" & ActiveCell.Row
cellorderpaste = "AA" & ActiveCell.Row
End If
Range(cellSTBpaste).PasteSpecial
Windows("May as of 06-01-07.xls").Activate
Range(cellorder).Copy
Windows("VOD Master List as of 06-19-07.xls").Activate
Range(cellorderpaste).PasteSpecial

Tom Ogilvy
Help with type mismatch error
 
Perhaps:

Dim rng as Range

x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find( _
What:=x, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows,_
SearchDirection:=xlNext, _
MatchCase:=False)
If Not c Is Nothing Then
Application.Goto c, True
Else
Exit Sub
End If
selection.Copy


--
regards,
Tom Ogilvy


" wrote:

Hi all,

I've posted about this problem later last week, but I still haven't
been able to fix up my code. Thanks for all the help, I'm really not
too great figuring out the errors.

The issue is going to be in the Set c = Range... line up until the End
If line. All of the other code has previously worked until I added
this new part. Basically the code is trying to copy and paste certain
data from one spreadsheet to another in a specific location. This one
part is trying to find the line in the other spreadsheet where it
should copy - depending a match within the G column. If it doesn't
find a match, it isn't supposed to copy anywhere. I'm currently
getting a type mismatch error on the Set c = Range... line. I really
don't know VB syntax at all, so I'm not sure what's wrong. Thanks!


Selection.Copy
x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
If Not c Is Nothing Then
c.Activate
Else
Exit Sub
End If
Windows("May as of 06-01-07.xls").Activate
datecomp = "A1"
cellshow = "B" & ActiveCell.Row
cellSTB = "H" & ActiveCell.Row
cellorder = "I" & ActiveCell.Row
testdate = "D" & ActiveCell.Row
Worksheets("Sheet5").Range(cellshow).Font.Italic = True
Worksheets("Sheet5").Range(cellshow).Font.Bold = True
Range(cellSTB).Copy
If (Range(testdate) Range(datecomp)) Then
Windows("VOD Master List as of 06-19-07.xls").Activate
cellSTBpaste = "Y" & ActiveCell.Row
cellorderpaste = "X" & ActiveCell.Row
Else
Windows("VOD Master List as of 06-19-07.xls").Activate
cellSTBpaste = "AB" & ActiveCell.Row
cellorderpaste = "AA" & ActiveCell.Row
End If
Range(cellSTBpaste).PasteSpecial
Windows("May as of 06-01-07.xls").Activate
Range(cellorder).Copy
Windows("VOD Master List as of 06-19-07.xls").Activate
Range(cellorderpaste).PasteSpecial

[email protected]
Help with type mismatch error
 
I believe this is what you wanted me to have. I tried both with/
without the SearchFormat:= False - I wasn't sure if you wanted me to
get rid of that. I'm still getting a typemismatch error on the Set
c... line. I did notice that it is finding the correct line within my
other spreadsheet before the error happens though.

When I'm running the code for something where it shouldn't find a
match, I'm getting an "Object variable or With block variable not set"
error.

Thanks!

Selection.Copy
Dim rng As Range
x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
If Not c Is Nothing Then
Application.Goto c, True
Else
Exit Sub
End If

On Jul 9, 11:02 am, Tom Ogilvy
wrote:
Perhaps:

Dim rng as Range

x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find( _
What:=x, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows,_
SearchDirection:=xlNext, _
MatchCase:=False)
If Not c Is Nothing Then
Application.Goto c, True
Else
Exit Sub
End If
selection.Copy

--
regards,
Tom Ogilvy



" wrote:
Hi all,

I've posted about this problem later last week, but I still haven't
been able to fix up my code. Thanks for all the help, I'm really not
too great figuring out the errors.

The issue is going to be in the Set c = Range... line up until the End
If line. All of the other code has previously worked until I added
this new part. Basically the code is trying to copy and paste certain
data from one spreadsheet to another in a specific location. This one
part is trying to find the line in the other spreadsheet where it
should copy - depending a match within the G column. If it doesn't
find a match, it isn't supposed to copy anywhere. I'm currently
getting a type mismatch error on the Set c = Range... line. I really
don't know VB syntax at all, so I'm not sure what's wrong. Thanks!

Selection.Copy
x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
If Not c Is Nothing Then
c.Activate
Else
Exit Sub
End If
Windows("May as of 06-01-07.xls").Activate
datecomp = "A1"
cellshow = "B" & ActiveCell.Row
cellSTB = "H" & ActiveCell.Row
cellorder = "I" & ActiveCell.Row
testdate = "D" & ActiveCell.Row
Worksheets("Sheet5").Range(cellshow).Font.Italic = True
Worksheets("Sheet5").Range(cellshow).Font.Bold = True
Range(cellSTB).Copy
If (Range(testdate) Range(datecomp)) Then
Windows("VOD Master List as of 06-19-07.xls").Activate
cellSTBpaste = "Y" & ActiveCell.Row
cellorderpaste = "X" & ActiveCell.Row
Else
Windows("VOD Master List as of 06-19-07.xls").Activate
cellSTBpaste = "AB" & ActiveCell.Row
cellorderpaste = "AA" & ActiveCell.Row
End If
Range(cellSTBpaste).PasteSpecial
Windows("May as of 06-01-07.xls").Activate
Range(cellorder).Copy
Windows("VOD Master List as of 06-19-07.xls").Activate
Range(cellorderpaste).PasteSpecial- Hide quoted text -

- Show quoted text -

[email protected]
Help with type mismatch error
 
Just noticed that you removed the .Activate. Makes sense now. Thanks!

On Jul 9, 11:18 am, wrote:
I believe this is what you wanted me to have. I tried both with/
without the SearchFormat:= False - I wasn't sure if you wanted me to
get rid of that. I'm still getting a typemismatch error on the Set
c... line. I did notice that it is finding the correct line within my
other spreadsheet before the error happens though.

When I'm running the code for something where it shouldn't find a
match, I'm getting an "Object variable or With block variable not set"
error.

Thanks!

Selection.Copy
Dim rng As Range
x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
If Not c Is Nothing Then
Application.Goto c, True
Else
Exit Sub
End If

On Jul 9, 11:02 am, Tom Ogilvy
wrote:



Perhaps:

Dim rng as Range

x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find( _
What:=x, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows,_
SearchDirection:=xlNext, _
MatchCase:=False)
If Not c Is Nothing Then
Application.Goto c, True
Else
Exit Sub
End If
selection.Copy

--
regards,
Tom Ogilvy

" wrote:
Hi all,

I've posted about this problem later last week, but I still haven't
been able to fix up my code. Thanks for all the help, I'm really not
too great figuring out the errors.

The issue is going to be in the Set c = Range... line up until the End
If line. All of the other code has previously worked until I added
this new part. Basically the code is trying to copy and paste certain
data from one spreadsheet to another in a specific location. This one
part is trying to find the line in the other spreadsheet where it
should copy - depending a match within the G column. If it doesn't
find a match, it isn't supposed to copy anywhere. I'm currently
getting a type mismatch error on the Set c = Range... line. I really
don't know VB syntax at all, so I'm not sure what's wrong. Thanks!

Selection.Copy
x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
If Not c Is Nothing Then
c.Activate
Else
Exit Sub
End If
Windows("May as of 06-01-07.xls").Activate
datecomp = "A1"
cellshow = "B" & ActiveCell.Row
cellSTB = "H" & ActiveCell.Row
cellorder = "I" & ActiveCell.Row
testdate = "D" & ActiveCell.Row
Worksheets("Sheet5").Range(cellshow).Font.Italic = True
Worksheets("Sheet5").Range(cellshow).Font.Bold = True
Range(cellSTB).Copy
If (Range(testdate) Range(datecomp)) Then
Windows("VOD Master List as of 06-19-07.xls").Activate
cellSTBpaste = "Y" & ActiveCell.Row
cellorderpaste = "X" & ActiveCell.Row
Else
Windows("VOD Master List as of 06-19-07.xls").Activate
cellSTBpaste = "AB" & ActiveCell.Row
cellorderpaste = "AA" & ActiveCell.Row
End If
Range(cellSTBpaste).PasteSpecial
Windows("May as of 06-01-07.xls").Activate
Range(cellorder).Copy
Windows("VOD Master List as of 06-19-07.xls").Activate
Range(cellorderpaste).PasteSpecial- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Tom Ogilvy
Help with type mismatch error
 
Looks like you missed the whole point of taking activate off the end of the
command.

--
Regards,
Tom Ogilvy


" wrote:

I believe this is what you wanted me to have. I tried both with/
without the SearchFormat:= False - I wasn't sure if you wanted me to
get rid of that. I'm still getting a typemismatch error on the Set
c... line. I did notice that it is finding the correct line within my
other spreadsheet before the error happens though.

When I'm running the code for something where it shouldn't find a
match, I'm getting an "Object variable or With block variable not set"
error.

Thanks!

Selection.Copy
Dim rng As Range
x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
If Not c Is Nothing Then
Application.Goto c, True
Else
Exit Sub
End If

On Jul 9, 11:02 am, Tom Ogilvy
wrote:
Perhaps:

Dim rng as Range

x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find( _
What:=x, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows,_
SearchDirection:=xlNext, _
MatchCase:=False)
If Not c Is Nothing Then
Application.Goto c, True
Else
Exit Sub
End If
selection.Copy

--
regards,
Tom Ogilvy



" wrote:
Hi all,

I've posted about this problem later last week, but I still haven't
been able to fix up my code. Thanks for all the help, I'm really not
too great figuring out the errors.

The issue is going to be in the Set c = Range... line up until the End
If line. All of the other code has previously worked until I added
this new part. Basically the code is trying to copy and paste certain
data from one spreadsheet to another in a specific location. This one
part is trying to find the line in the other spreadsheet where it
should copy - depending a match within the G column. If it doesn't
find a match, it isn't supposed to copy anywhere. I'm currently
getting a type mismatch error on the Set c = Range... line. I really
don't know VB syntax at all, so I'm not sure what's wrong. Thanks!

Selection.Copy
x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
If Not c Is Nothing Then
c.Activate
Else
Exit Sub
End If
Windows("May as of 06-01-07.xls").Activate
datecomp = "A1"
cellshow = "B" & ActiveCell.Row
cellSTB = "H" & ActiveCell.Row
cellorder = "I" & ActiveCell.Row
testdate = "D" & ActiveCell.Row
Worksheets("Sheet5").Range(cellshow).Font.Italic = True
Worksheets("Sheet5").Range(cellshow).Font.Bold = True
Range(cellSTB).Copy
If (Range(testdate) Range(datecomp)) Then
Windows("VOD Master List as of 06-19-07.xls").Activate
cellSTBpaste = "Y" & ActiveCell.Row
cellorderpaste = "X" & ActiveCell.Row
Else
Windows("VOD Master List as of 06-19-07.xls").Activate
cellSTBpaste = "AB" & ActiveCell.Row
cellorderpaste = "AA" & ActiveCell.Row
End If
Range(cellSTBpaste).PasteSpecial
Windows("May as of 06-01-07.xls").Activate
Range(cellorder).Copy
Windows("VOD Master List as of 06-19-07.xls").Activate
Range(cellorderpaste).PasteSpecial- Hide quoted text -

- Show quoted text -


All times are GMT +1. The time now is 12:13 PM.

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