Solved: Calling a sub procedure using variables from another sub procedure

[jazzyt2u]

I'm trying to cut down on repetitive coding. I have different sections of an excel spreadsheet that needs to clean up data and then copy it over to Word.

I have declared or named the word application and file earlier in the main Sub. When I go to the called sub and then paste data into the word document it leaves that sub and goes back to the main one.


Sub Demo()

Dim oWord As Object
Dim oDoc As Object


Dim rngT130 As Range
Dim rngT140 As Range
Dim rngT150 As Range
Dim rngT160 As Range

Dim rngT1000 As Range
Dim rngT1100 As Range
Dim rngT1200 As Range
Dim rngT1300 As Range
Dim rngT1400 As Range

Set oWord = CreateObject("Word.Application") 'Create an instance of word
Set oDoc = oWord.Documents.Open("H:\TashaA\Test2\AvonReportTemplate_AA.doc") 'Open word file
oWord.Visible = True

'I have coding here that copies other worksheet data over to Word

Sheets("DemoTable").Select
Range("F1").Select
oDoc.Bookmarks("DemoTable").Select


Columns("B:B").Select
On Error Resume Next
Selection.Find(What:="Region", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

If ActiveCell = "Region" Then
Paste2Word
End If

Columns("B:B").Select
On Error Resume Next
Selection.Find(What:="Country", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

If ActiveCell = "Country" Then
Paste2Word
End If

Columns("B:B").Select
On Error Resume Next
Selection.Find(What:="Location", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

If ActiveCell = "Location" Then
Paste2Word
End If

End Sub

Sub Paste2Word()
ActiveCell.Offset(2, 0).Select
Set rngT130 = Selection
Dim rngD130 As Range
Dim rngTD130 As Range

ActiveCell.Offset(12, 1).Select
Set rngD130 = Selection
Set rngTD130 = Range(rngT130, rngD130)



Do Until ActiveCell = "" Or ActiveCell.Offset(0, 1) = ""

ActiveCell.Offset(-12, 1).Select
Set rngT140 = Selection
Selection.End(xlDown).Select
Do Until rngTD130.Width + Range(rngT140, ActiveCell).Width > 540 Or ActiveCell.Offset(0, 1) = ""
ActiveCell.Offset(0, 1).Select

Loop
Set rngD140 = Selection
Set rngTD140 = Range(rngT140, rngD140)

Worksheets("DemoTable").Range(rngT130, rngD130).CopyPicture xlScreen, xlBitmap
oWord.Selection.PasteAndFormat Type:=wdFormatOriginalFormatting 'AS SOON AS IT GETS HERE IT GOES BACK TO THE Sub DEMO STATEMENT
Worksheets("DemoTable").Range(rngT140, rngD140).CopyPicture xlScreen, xlBitmap
oWord.Selection.PasteAndFormat Type:=wdFormatOriginalFormatting
oWord.Selection.TypeParagraph

Loop
End Sub


Please help. I don't there's more code that I'm showing in the Sub Paste2Word and I don't want to have to repeat it for each section.

[Mavyak]

You have a lot of "On Error Resume Next" statements. That statement (aside from being dangerous programming) need only exist once as the first statement in your sub-procedure. I suspect that an error is occurring and you are not aware of it because the code is resuming at the next statement, which likely generates an error but again, resumes at the next statement (and so on, and so on, etc.). Try removing the "On Error Resume Next" statements so that your code will crash. Select "Debug" when it does and let us know at what line it is failing.

Mavyak

[jazzyt2u]

Thank you for the tip in regards to the "On Error Resume Next" statement. I put in the code where the problem is occurring... it's in the second sub when it trys to process the line "oWord...." oWord is defined in the first sub but I need to use it in the second sub too...
Is there a way to do this????

[jazzyt2u]

Also what would you suggest to use when trying to find something and it's not there. The system will generate an error but I don't want it to shut down the whole process if it can't find what it's looking for... I just want it to go find the next word...

[Mavyak]

Change your sub procedure definition from this:
Sub Paste2Word()
to this:
Sub Paste2Word(ByRef oWord As Object)
Then, wherever you call the Paste2Word sub-procedure, call it like this:
Paste2Word oWord
To solve the check for not-found issue, you will need to declare a variable of type Range and then set that variable to the search results like so:

Dim Something As Range

Set Something = Selection.Find(What:="Region", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)

If Not Something Is Nothing Then
If Something = "Region" Then
Something.Activate
Paste2Word oWord
End If
End If

[jazzyt2u]

You are so AWESOME!!!!!!!!!!! That worked like a charm....

I found another work around for the error. I did an "On Error go to ErrorHandler" since I need one for other coding I have in this procedure... and within that error handler I tell it to resume next. Then I use "On error go to 0" right before I call the other sub procedure and it turns it off. I'll see how this works but if It doesn't I'll do what you suggested above...

Again...thanks SO MUCH!!!!

Quick question am I able to use the same DIM statements between two procedures when one calls the other????

[Mavyak]

No. Variables have a thing called "scope". When you declare a varriable in a sub-procedure, it exists only for that sub-procedure. That's why we had to pass your oWord variable to the Paste2Word sub-procedure (because it didn't exist there until we sent it there). You could have declared the oWord variable outside of your procedure. At that point it would have been "Global", which means it would have been available to all procedures/functions. But that is usually frowned upon.

[jazzyt2u]

Okay...And I did change to the declaring a range instead of what I did and it too is awesome...:bow:

[jazzyt2u]

Hi one more question : pray2:

If the following:

If Not FindWord Is Nothing Then
If FindWord = "Years of Service" Then
FindWord.Activate
PasteInWord oWord
End If
End If

Why is it "If Not" FindWord is Nothing Then and not "If" FindWord is Nothing Then :think:

[Mavyak]

Because we don't need to do anything if nothing is found.

If FindWord Is Nothing Then
'What do we do here?
Else
If FindWord = "Years of Service" Then
FindWord.Activate
PasteInWord oWord
End If
End If

[jazzyt2u]

Oh cool...wanted to know why and now that will help me with other stuff...:thumb
Gosh how do I figure out all of this stuff...LOL