Msgbox Help

[John Davis]

Hello All:

I'm trying to put together the following routine. I get an error message subscript of of range on the line with Range("E20"). Any help would be appreciated.

Sub TSCA()

Dim vResult As String
vResult = MsgBox("Is there a TSCA required?", vbYesNo)
If vResult = vbYes Then Workbooks.OpenText Filename:="D:\Common\data\excel\OCEAN-TSCA.xls", Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
Range("E20") = Windows("DLY-LOGISTICS.xls")(Range("G5000") & Range("H5000")) '
Windows("OCEAN-TSCA.xls").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

If vResult = vbNo Then Exit Sub

End Sub

 

[AlexM]

Looks like there is no "DLY-LOGISTICS.xls" window. You can enumerate all elements in Windows collection to check this. Or check it in the Watch window in VBE.

 

[VoG]

Range("E20") = Windows("DLY-LOGISTICS.xls")(Range("G5000") & Range("H5000")) '

should, I think (as I have no idea what you are trying to do) be something like

Range("E20").Value = Workbooks("DLY-LOGISTICS").Sheets("Sheet1").Range("G5000") & Workbooks("DLY-LOGISTICS").Sheets("Sheet1").Range("H5000")

but you probably also need to qualify the workbook and sheet in which E20 resides.

 

[John Davis]

Range("E20") = Windows("DLY-LOGISTICS.xls")(Range("G5000") & Range("H5000")) '

should, I think (as I have no idea what you are trying to do) be something like

Range("E20").Value = Workbooks("DLY-LOGISTICS").Sheets("Sheet1").Range("G5000") & Workbooks("DLY-LOGISTICS").Sheets("Sheet1").Range("H5000")

but you probably also need to qualify the workbook and sheet in which E20 resides.

Thanks for your reply. In the active workbook OCEAN-TSCA range E20 I'm trying to place the concatenated cell values of G5000 & H5000 from workbook DLY-LOGISTICS. I haven't tried it yet, but I think your suggestion might work.