Hello,
I'm newer to VBA and I'm trying to write a concatenate program for multiple contiguous cells in column A. The data always starts with a key word (in this case "item"...in lines 1, 11, and 21 in the below data example), but may be different numbers of cells long.
I have been trying to use loops and Instr to concatenate all cells between "item" markers and print the concatenated cells to a single cell in column B for each group.
So far it works well the first time through printing to B1.
Then the 2nd time through the below section of code ignores variables Y and Z, and prints (incorrect) A1 to A20 to cell B2, rather than the content from A11 to A20 as intended. I checked the values with message boxes before that section, and the values are correct..so I'm a bit confused.
' Concatenate cells between rows Y and Z in column A.
Set SourceRange = Range("A" & Y & ":A" & Z - 1)
For Each Rng In SourceRange
i = i & Rng & " "
Next Rng
Range("B" & Row2).Value = Trim(i)
The third time it crashes with a "runtime error 6 overflow". (My best guess is that the range variable has a character limit which it hit trying to combine cells 1-30 (568 characters)? )
Please let me know if you can help to correct what I'm doing wrong, or point me in the right direction.
Thank you.
Kaitlyn
.
Spreadsheet data:
Item 2360:901512020
Purple bulk
ADDITIONAL DATA:
one-eyed, one-horned,
DRAWING ST4
B4079B flying purple people
eater TC-407
Generic Soylet Purple Co
P/N#0024-LMO-10-157
- Quantity : 5 EA
Item 2370:901512020
A tisket-a-tasket LK,#00024L9020
ADDITIONAL DATA:
A green and yellow basket
DRAWING ST11
B4060B I dropped it
Yes on the way I dropped it SUS TC-404
Generic Basket Weaving Co
P/N#0024-LRO-10-206
- Quantity : 16 EA
Item 2380:9011201208
Porcelain Tub
ADDITIONAL DATA:
Splish Splash
DRAWING ST4
B4010B Takin a bath
down the drain TC-4657
Water Works Inc
P/N#00024-LOW-14-063
- Quantity : 2 EA
Sub CombineRanges()
'Declare variables
Dim LastRow As Integer
Dim Row1 As Integer
Dim Row2 As Integer
Dim Y As Integer
Dim Z As Integer
Dim SourceRange As Range
Dim Rng As Range
Dim i As String
Sheets("Input").Select
'Define First and Last Rows
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Y = 1
Row1 = 2
Row2 = 1
'Application.ScreenUpdating = False
'Loop Through Rows (Top to Bottom) while Row1 (counter) is less than LastRow
Do
' Check for Keyword at beginning of cell.
' If Keyword not found, continue loop. Add 1 to Row1 counter. Do "While" loop again.
' If/when found, Z = Row1
Do
If InStr(1, Range("A" & Row1), "Item", vbTextCompare) = 1 Then
Z = Row1
Else: Row1 = Row1 + 1
End If
Loop While InStr(1, Range("A" & Row1), "Item", vbTextCompare) <> 1
Z = Row1
' Checking variable values. Values are correct, but next section of code ignores "Y" variable and
' defines Sourcerange from A1 to A20
MsgBox "The value of Y is " & Y, vbInformation
MsgBox "The value of Z is " & Z, vbInformation
'Set SourceRange = Nothing
' Concatenate cells between rows Y and Z in column A.
Set SourceRange = Range("A" & Y & ":A" & Z - 1)
For Each Rng In SourceRange
i = i & Rng & " "
Next Rng
Range("B" & Row2).Value = Trim(i)
'Y is redefined as old Z value for next loop.
Y = Z
'Row1 and Row2 are redefined for next loop.
Row1 = Row1 + 1
Row2 = Row2 + 1
Loop While Row1 < LastRow
'Application.ScreenUpdating = True
End Sub
.