Don't forget that there are MSWord newsgroups, too.

They're not as friendly <vbg. But they're pretty darn smart!

HeatherO wrote:

Thanks a million Dave. I really appreciate all your help with this. Wish me
luck with the rest of the project. This is just part A and then I have to
teach myself about using word with excel and mail merges and stuff. Nothing
like jumping in without checking how deep. Oh well, it's all learning. I
had no idea excel and vb could do all this stuff it's very exciting. I have
programmed in RPG and progress mostly so this is very new.
Thanks again.

"Dave Peterson" wrote:

One way:

dim Res as variant 'can contain an error

res = _
Application.VLookup(lokval, _
Workbooks("ccs76463097.xls").Sheets("SMART").Range ("$A$2:$G$54"), _
7, False)

if iserror(res) then
Cells(counter, 14).Value = "missing"
else
cells(counter,14).value = res
end if

HeatherO wrote:

Thank you so much that worked. Of course I do get the #N/A error if there is
no match found in the ccs76463097 workbook. Just off the top of your head is
there any way I can get around this or not really. I think there is
something though. I'll go search. I'm not too worried about it though.
Thanks so much.
Heather

"Dave Peterson" wrote:

If you're still getting that subscript error, you'll have to check your
typing--both the workbook name and worksheet name.

And this kind of format may be better (to stop the run time errors).

Cells(counter, 14).Value = _
Application.VLookup(lokval, _
Workbooks("ccs76463097.xls").Sheets("SMART").Range ("$A$2:$G$54"), _
7, False)

Dropping the .worksheetfunction. allows you to bring back the #n/a errors.

In this section, it looked like you had an extra leading space:

Application.WorksheetFunction.VLookup(lokval, Workbooks("C:\Documents and
Settings\My Documents\ dcs76463097.xls").Sheets("SMART").Range("$A$2:$G$5 4"),
7, False)

(right before the dcs76... stuff.)


HeatherO wrote:

The workbooks are open here is a copy of the full procedure so you can see
but I make sure the workbooks exist and are open before I do the lookup. I
just wrote out the full path name in case that was my problem in the vlookup.

Private Sub CommandButton1_Click()

Dim Dlrno As String
Dim Repno As String
Dim RSM As Integer
Dim Dlrshp As String
Dim PrepFor As String
Dim dlrrep As String
Dim ccswrkbk As Workbook
Dim dcswrkbk As Workbook
Dim wks As Worksheet
Dim lval As String
Dim rngscs As Range
Dim rngtouse As Range
Dim mycell As Range
Dim mainwks As Worksheet
Dim sPath As String
Dim sMyFile As String
Dim ccsfile As String
Dim dcsfile As String
Dim lokval As String
Dim debg As String


ccsfile = "no"
dcsfile = "no"


dlrrep = UserForm1.Dlrno + UserForm1.Repno


'Activate the correct worksheet based on English or French.
If EnglishButton1 Then

Workbooks("Pro Forma.xlt").Worksheets("English").Activate
With ActiveSheet
.Range("B1").Value = UserForm1.Dlrno
.Range("B2").Value = UserForm1.Repno
.Range("B1:B2").NumberFormat = "General"
.Range("B3").Value = UserForm1.PrepFor
.Range("B4").Value = UserForm1.Dlrshp
.Range("B5").Value = UserForm1.RSM
End With
Set mainwks = Workbooks("Pro Forma.xlt").Worksheets("English")

ElseIf FrenchButton1 Then


Workbooks("Pro Forma.xlt").Worksheets("French").Activate
With ActiveSheet
.Range("B1").Value = UserForm1.Dlrno
.Range("B2").Value = UserForm1.Repno
.Range("B1:B2").NumberFormat = "General"
.Range("B3").Value = UserForm1.PrepFor
.Range("B4").Value = UserForm1.Dlrshp
.Range("B5").Value = UserForm1.RSM
End With
Set mainwks = Workbooks("Pro Forma.xlt").Worksheets("French")

End If

'get values for both CCS and DCS and store in column "N" of Auto Model
Grid

Set wks = Workbooks("Auto Model Grid").Worksheets("sheet1")


sPath = "C:\Documents and Settings\My Documents\"
sMyFile = "CCS" & dlrrep & ".xls"
If Dir(sPath & sMyFile) = "" Then
ccsfile = "no"
Else: ccsfile = "yes"

End If

sMyFile = "DCS" & dlrrep & ".xls"
If Dir(sPath & sMyFile) = "" Then
dcsfile = "no"
Else: dcsfile = "yes"

End If


If ccsfile = "yes" Then
Set ccswrkbk = Workbooks.Open("C:\Documents and Settings\My
Documents\CCS" & dlrrep & ".xls")
End If

If dcsfile = "yes" Then
Set dcswrkbk = Workbooks.Open("C:\Documents and Settings\My
Documents\DCS" & dlrrep & ".xls")
End If


'get values from dcs and ccs and put in col N of Auto Model Grid
Workbooks("Auto Model Grid.xls").Worksheets("Sheet1").Activate
With ActiveSheet

For counter = 2 To 55
Set mycell = Worksheets("Sheet1").Cells(counter, 12)

lval = mycell
If lval = "CCS" Then
If ccsfile = "yes" Then
lokval = Cells(counter, 2).Value
Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval, Workbooks("C:\Documents and
Settings\My Documents\ccs76463097.xls").Sheets("SMART").Range( "$A$2:$G$54"),
7, False)
End If

ElseIf lval = "DCS" Then
If dcsfile = "yes" Then
lokval = Cells(counter, 2).Value
Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval, Workbooks("C:\Documents and
Settings\My Documents\ dcs76463097.xls").Sheets("SMART").Range("$A$2:$G$5 4"),
7, False)
End If
End If
Next counter
End With



If EnglishButton1 Then
Set mainwks = Workbooks("Pro Forma.xlt").Worksheets("English")
ElseIf FrenchButton1 Then
Set mainwks = Workbooks("Pro Forma.xlt").Worksheets("French")
End If

UserForm1.Hide
Application.Visible = True

End

End Sub

"Tom Ogilvy" wrote:

when you use vlookup in VBA, you can't access a closed workbook.

There are clumsy workarounds, but is there some reason can't open the
workbook?

when opened, of course, it would be Workbooks("ccs76463097.xls").

--
Regards,
Tom Ogilvy



"HeatherO" wrote in message
...
Hi Tom,
Here is my code as I run it now. I've highlighted the spot where it
stops
With **** and I get the run-time error '9': Subscript is out of Range
message.
Any help is appreciated.
Heather
'get values from ccs and dcs and put in col N of Auto Grid
Workbooks("Auto Model Grid.xls").Worksheets("Sheet1").Activate
With ActiveSheet

For counter = 2 To 55
Set mycell = Worksheets("Sheet1").Cells(counter, 12)

lval = mycell
If lval = "CCS" Then
If ccsfile = "yes" Then
lokval = Cells(counter, 2).Value
*****
Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval, Workbooks("C:\Documents and
Settings\My
Documents\ccs76463097.xls").Sheets("SMART").Range( "$A$2:$G$54"),
7, False)
****
End If

ElseIf lval = "DCS" Then
If dcsfile = "yes" Then
lokval = Cells(counter, 2).Value
Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval, Workbooks("C:\Documents and
Settings\My
Documents\dcs76463097.xls").Sheets("SMART").Range( "$A$2:$G$54"),
7, False)
End If
End If
Next counter
End With

"Tom Ogilvy" wrote:

In the code you show, I believe only one thing can cause the error you
cite:

run-time error 9: subscript out of range

This is if the name of one of the objects is incorrect. workbooks
names
should always have .xls on the end (if you haven't maintained the
correction
I suggested).

However, perhaps your error has changed. Post your latest code and
indicate
which line is highlighted when you get the error and what the text and
number of the error is.

--
Regards,
Tom Ogilvy

"HeatherO" wrote in message
...
Thanks Dave. I did check and I did have the sheet name incorrect. I
guess
they changed the name of the sheet with my test workbook. However
after I
made the changes I still got the errors. I've been looking it over
and
tried
changing the range and I am now at a complete loss. Funny because I
have
to
let them know how I am progressing with this project tommorrow. I'll
keep
trying to plug away to see if I can find someway around this. Thanks
though
it did help.
Heather

"Dave Peterson" wrote:

When I get that subscript out of range, it usually means that I've
made
a typo.

If you're code is breaking on that vlookup() line, I'd double/triple
check the
spelling of the worksheet Smart (any extra spaces--leading or
trailing?).


HeatherO wrote:

Tried it and I am still getting the same error message. I've
debuged
it all
the way to the lookup function. It is returning all the correct
values from
the active sheet but it just barfs when it gets to the code with
the
vlookup.
The workbook is open it just does not like it.
Thanks again for any help
Heather O

"Tom Ogilvy" wrote:

Try this.

Workbooks("Auto Model Grid.xls").activate
Worksheets("Sheet1").Activate


--
Regards,
Tom Ogilvy


"HeatherO" wrote in message
...
I did that and I still get the error:
run-time error 9: subscript out of range

Here is my code now as I have modified it but am still stumped
on
the
lookup. I have even hard coded the workbooks to make sure it
looked at
the
right ones, I don't know if I need to specify the path but I
will
try that
next.

Workbooks("Auto Model Grid").Worksheets("Sheet1").Activate
With ActiveSheet

For counter = 2 To 55
Set mycell = Worksheets("Sheet1").Cells(counter, 12)

lval = mycell
If lval = "CCS" Then
If ccsfile = "yes" Then
lokval = Cells(counter, 2).Value
ActiveSheet.Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval,

Workbooks("CCS76463097.xls").Sheets("SMART").Range ("$A$2:$H$82"),
7, 0)
End If

ElseIf lval = "DCS" Then
If dcsfile = "yes" Then
lokval = Cells(counter, 2).Value
ActiveSheet.Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval,

Workbooks("DCS76463097.xls").Sheets("SMART").Range ("$A$2:$H$82"),
7, 0)


End If
End If
Next counter
End With

"Dave Peterson" wrote:

Maybe specifying the workbook that contains the Smart
worksheet:

Sheets("SMART").Range("$A$2:$H$82")

becomes:


workbooks("myotherbook.xls").Sheets("SMART").Range ("$A$2:$H$82")

in your vlookup() formula.

If that's not the problem, what line causes the error?

HeatherO wrote:

I have this as my code:
Workbooks("Auto Model
Grid").Worksheets("Sheet1").Activate
With ActiveSheet

For counter = 2 To 55
Set mycell = Worksheets("Sheet1").Cells(counter,
12)

lval = mycell
If lval = "CCS" Then
If ccsfile = "yes" Then
mycell.Offset(counter, 14).Value =
Application.Worksheet.VLookup("CCS" & dlrrep,
Sheets("SMART").Range("$A$2:$H$82"), 7, 0)
End If

ElseIf lval = "DCS" Then
If dcsfile = "yes" Then
mycell.Offset(counter, 14).Value =
Application.WorksheetFunction.VLookup("DCS" & dlrrep,
Sheets("SMART").Range("$A$2:$H$82"), 7, 0)
End If
End If
Next counter
End With

For some reason when I do the vlookup I am getting an
error
message
saying
the subscript is out of range. Is this because I am in
another
workbook that
it can't do the lookup on the other workbook or is it
because
I am
trying to
string together a string plus a variable as the workbook's
name? Any
thoughts would be welcome.
Thanks again in advance.
Heather O

--

Dave Peterson





--

Dave Peterson








--

Dave Peterson


--

Dave Peterson


--

Dave Peterson