PDA

View Full Version : Run-time error '438': Object doesn't support this property or method.



eyesonthebal
07-20-2015, 07:42 AM
Hello,

I've written the below code for a macro that I want to operate as follows:

Given a particular set value for the cell variable 'RepNum' (located in cell B1) in my 'Input' worksheet, upon clicking the 'Print' button, all documents pertaining to that particular representative number will print out. The parameters for each document are 'RepNum', 'GroupNum' and 'DivNum'. All three of these variables are located in the 'Input' worksheet of my workbook. There are several combinations of values of 'GroupNum' and 'DivNum' for each particular value of 'RepNum'. There will be one document for each unique combination of 'GroupNum' and 'DivNum'. I've defined these cell variables as sub variables in the below code as well, although I think this may be a bit superfluous. Regardless, for some reason, I hit an error at the line: "If Worksheets("Data").Cell(Row,8).Value = RepNum Then". I have no experience with VBA, and so I'm essentially just trying to wing it, here. If someone could help me out, it would be greatly appreciated. Also, if you have any questions you'd like answered before you elaborate on anything, please ask. Thank you!

David

Sub Print_Click()
Dim Row As Integer
Dim RepNum As Integer
Dim GroupNum As Integer
Dim DivNum As Integer
Row = 9
RepNum = Worksheets("Input").Range("B1").Value
Do
If Worksheets("Data").Cell(Row, 8).Value = RepNum Then
GroupNum = Worksheets("Data").Cell(Row, 3).Value
Worksheets("Input").Range("A7").Value = GroupNum
DivNum = Worksheets("Data").Cell(Row, 4).Value
Worksheets("Input").Range("A8").Value = DivNum
Worksheets("RenewalPacket").PrintOut
Row = Row + 1

Else
Row = Row + 1

End If

Loop Until IsEmpty(Worksheets("Data").Cell(Row, 8))
End Sub

p45cal
07-20-2015, 08:00 AM
change all instances of Cell to Cells.

Yes those 2 variables are a bit superfluous, but RepNum isn't.

Also, since Row = Row + 1 is executed in both parts of the IF statement then it is always executed, so can be outside the IF statement.:
Sub Print_Click()
Dim Row As Long
Dim RepNum As Long
Row = 9
RepNum = Worksheets("Input").Range("B1").Value
Do
If Worksheets("Data").Cells(Row, 8).Value = RepNum Then
Worksheets("Input").Range("A7").Value = Worksheets("Data").Cells(Row, 3).Value
Worksheets("Input").Range("A8").Value = Worksheets("Data").Cells(Row, 4).Value
Worksheets("RenewalPacket").PrintOut
End If
Row = Row + 1
Loop Until IsEmpty(Worksheets("Data").Cells(Row, 8))
End Sub

eyesonthebal
07-20-2015, 08:17 AM
change all instances of Cell to Cells.

Yes those 2 variables are a bit superfluous, but RepNum isn't.

Also, since Row = Row + 1 is executed in both parts of the IF statement then it is always executed, so can be outside the I statement.:
Sub Print_Click()
Dim Row As Long
Dim RepNum As Long
Row = 9
RepNum = Worksheets("Input").Range("B1").Value
Do
If Worksheets("Data").Cells(Row, 8).Value = RepNum Then
Worksheets("Input").Range("A7").Value = Worksheets("Data").Cells(Row, 3).Value
Worksheets("Input").Range("A8").Value = Worksheets("Data").Cells(Row, 4).Value
Worksheets("RenewalPacket").PrintOut
End If
Row = Row + 1
Loop Until IsEmpty(Worksheets("Data").Cells(Row, 8))
End Sub



Such a simple solution! Thank you for the informative reply and tidying up of my code!

eyesonthebal
07-20-2015, 08:56 AM
I'd actually like to take this one step further, now. When the macro prints out each document, I want it to print it out as a pdf, with a particular name and in a particular folder. I'm not sure how I should do this. So far, I've made the following change:

Worksheets("RenewalPacket").Printout --> Worksheets("RenewalPacket").Printout ActivePrinter := "ScanSoft PDF Create!", PrintToFile := True, PrToFileName:= "Filename"

When I run the macro, though, it doesn't actually automatically name the pdf file and save it. I have to manually name the file and save it where I'd like. How would I go about troubleshooting this? Again, thank you for your help. It's GREATLY appreciated.

p45cal
07-20-2015, 10:20 AM
I would guess something like:

Worksheets("RenewalPacket").PrintOut ActivePrinter:="ScanSoft PDF Create!", PrintToFile:=True, PrToFileName:="C:\Users\Public\Documents\Book2.pdf
but this will name it the same every time. How do you want to make up the file name?
Suggestion: Name the file from the repnum, divnum and grpnum (which means bringing back your variables!).
Depending on which version of Excel you have, there is a built-in pdf format a sheet can be saved in, so you could try out the commented-out line below the .printout line in the code below (of course you should adjust the folder/path to your liking first):
Sub Print_Click()
Dim Row As Long
Dim GroupNum As Long
Dim DivNum As Long
Dim RepNum As Long
Row = 9
RepNum = Worksheets("Input").Range("B1").Value
Do
If Worksheets("Data").Cells(Row, 8).Value = RepNum Then
GroupNum = Worksheets("Data").Cells(Row, 3).Value
Worksheets("Input").Range("A7").Value = GroupNum
DivNum = Worksheets("Data").Cells(Row, 4).Value
Worksheets("Input").Range("A8").Value = DivNum
Worksheets("RenewalPacket").PrintOut ActivePrinter:="ScanSoft PDF Create!", PrintToFile:=True, PrToFileName:="C:\Users\Public\Documents\RepNum " & RepNum & " Grp " & GroupNum & " Div " & DivNum & ".pdf"
'Worksheets("RenewalPacket").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\Public\Documents\RepNum " & RepNum & " Grp " & GroupNum & " Div " & DivNum & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End If
Row = Row + 1
Loop Until IsEmpty(Worksheets("Data").Cells(Row, 8))
End Sub

eyesonthebal
07-20-2015, 10:42 AM
I would guess something like:

Worksheets("RenewalPacket").PrintOut ActivePrinter:="ScanSoft PDF Create!", PrintToFile:=True, PrToFileName:="C:\Users\Public\Documents\Book2.pdf
but this will name it the same every time. How do you want to make up the file name?
Suggestion: Name the file from the repnum, divnum and grpnum (which means bringing back your variables!).
Depending on which version of Excel you have, there is a built-in pdf format a sheet can be saved in, so you could try out the commented-out line below the .printout line in the code below (of course you should adjust the folder/path to your liking first):
Sub Print_Click()
Dim Row As Long
Dim GroupNum As Long
Dim DivNum As Long
Dim RepNum As Long
Row = 9
RepNum = Worksheets("Input").Range("B1").Value
Do
If Worksheets("Data").Cells(Row, 8).Value = RepNum Then
GroupNum = Worksheets("Data").Cells(Row, 3).Value
Worksheets("Input").Range("A7").Value = GroupNum
DivNum = Worksheets("Data").Cells(Row, 4).Value
Worksheets("Input").Range("A8").Value = DivNum
Worksheets("RenewalPacket").PrintOut ActivePrinter:="ScanSoft PDF Create!", PrintToFile:=True, PrToFileName:="C:\Users\Public\Documents\RepNum " & RepNum & " Grp " & GroupNum & " Div " & DivNum & ".pdf"
'Worksheets("RenewalPacket").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\Public\Documents\RepNum " & RepNum & " Grp " & GroupNum & " Div " & DivNum & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End If
Row = Row + 1
Loop Until IsEmpty(Worksheets("Data").Cells(Row, 8))
End Sub


Thanks once again, p45cal! For some reason, the PrintOut code doesn't work. The "Save as" window pops up, but the title has to be entered and the file has to be saved manually. However, the commented-out code that you provided me with works perfectly! Thank you VERY MUCH!!