Microsoft Excel - Runtime Error '9': Subscript out of range.
Asked By mario
15-Jul-11 08:14 AM
Hi, I have a script below where all I changed was:
from: varValue2 = Split(Range("D" & lngRow), " ")(0)
to: varValue2 = Split(Range("E" & lngRow), " ")(0)
And this results in an error message - Runtime Error '9': Subscript out of range.
Any suggestions why the change (from column D to column E) is failing the script? Please advise. Thanks.
========================================
Sub Macro()
Dim wsTarget As Worksheet, lngRow As Long, lngTRow As Long
Dim varValue1 As Variant, varValue2 As Variant
Set wsTarget = Sheets("Sheet2")
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Trim(Range("A" & lngRow)) Like "PO Line Item Number*" Then
varValue1 = Split(Range("C" & lngRow), " ")(0)
varValue2 = Split(Range("E" & lngRow), " ")(0)
ElseIf Trim(Range("A" & lngRow)) Like "Brief Description*" Then
lngTRow = lngTRow + 1
wsTarget.Range("A" & lngTRow) = Range("B" & lngRow)
wsTarget.Range("B" & lngTRow) = varValue1
wsTarget.Range("C" & lngTRow) = varValue2
End If
Next
End Sub
Ravinder Jamgotre replied to mario
You no target for column E, hence the out of range error. Increment the script accoridingly so varValue1, varValue2, varValue3, varValue4, varValue5 with subsequent column values for after splitRange and then repeat this in wsTarget. like below.
varValue1 = Split(Range("B" & lngRow), " ")(0)
varValue2 = Split(Range("C" & lngRow), " ")(0)
varValue3 = Split(Range("D" & lngRow), " ")(0)
varValue4 = Split(Range("E" & lngRow), " ")(0)
wsTarget.Range("A" & lngTRow) = Range("B" & lngRow)
wsTarget.Range("B" & lngTRow) = varValue1
wsTarget.Range("C" & lngTRow) = varValue2
wsTarget.Range("D" & lngTRow) = varValue3
wsTarget.Range("E" & lngTRow) = varValue4
Hope this helps.
mario replied to Ravinder Jamgotre
Thank you Ravinder, this worked nicely!
Subscript error message DataBase When I try to split my database I get a "subscript out of range" error message. How do I fix this? Access Discussions Database (1) Make sure to the Db (& Decompile it will not hurt either) before running the wizard HTH Pieter keywords: Subscript, error, message description: When I try to split my database I get a subscript out of
Font information in Net Hi All! I need following information about TrueType font: subscript horisontal size subscript vertical size subscript x offset subscript y offset superscript horisontal size superscript vertical size superscript x offset superscript y
Subscript Word Hello, is there a way to determine if a certain character is formatted as 'Substring'? thx Ralf Word Programming Discussions Office XP (1) Selection.Font.Subscript (1) Vista (1) Word (1) ActiveDocument.Range (1) Selection.Moveright (1) Selection.Collapse (1) WordVBA (1) Hi Ralf, perhaps: Sub Test() Selection.Collapse Selection.Moveright unit: = wdCharacter, Extend: = True If Selection.Font.Subscript Then MsgBox "subscript = true" End If End Sub - - Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA
In OneNote can you subscript a letter? Office I want to show a subscript like n1 with the 1 subscripted. One Note Discussions OneNote (1) MVP (1) FAQ (1) Subscript (1) Sojour (1) Microsoft (1) Rolandschorr (1) Formats (1) sojour@ says. . . The very second item in
Is it possible to use Subscript and superscript in excel? Excel Is it possible to use Subscript and superscript in excel? Excel Miscellaneous Discussions Excel 2003 (1) Strikethrough (1) Jack34daniels (1) Go to Format Cells, and choose the font tab. You should see 3 checkboxes for strikethrough, superscript, and subscript. - - Best Regards, Luke M *Remember to click "yes" if this post helped you!* Yes. (In xl2003