Hi. GTO, I'm really struggling getting that started. I pasted this query earlier but it fell off the board I guess. I'll paste my code below. I know some is rough, I'm working through it now but hung up. In a nutshell I want to loop through a worksheet that contains a web query. The query is rough in format (non- continuous) but there are continuous sections within it that i want to copy. I want to get to the range (see in code, finding the word "company"), copy it into an array with a loop, ending the loop on a blank cell, which resumes the outer loop that continues down looking for the word "company." Upon finding it again, the inner loop runs again, APPENDING/ ADDING TO the original data in the array. To append to the array, I think I should use worksheetfunction.count?? but how on just one "column" of the array?? *sigh.
I really hope this makes sense. I think I'd go a long way if I can get this to work and get the concepts behind it but I keep getting turned in other directions. Here's the code...
Dim DataArray() As Variant 'Main data array
Dim ArrayLength As Integer 'Array length
Dim UGQFinalRow As Long 'Final row of worksheet BriefingAnalystUpsDwnsQuery
Dim UGQFinalRowFixed As Long 'Final row of worksheet BriefingAnalystUpsDwnsQuery
Dim UGQNewRow As Long 'Final row of worksheet BriefingAnalystUpsDwnsQuery
Dim UGNewRow As Long 'Final row of worksheet BriefingAnalystUpsDwns
Dim UGQ As Worksheet 'Worksheet abbreviated reference BriefingAnalystUpsDwnsQuery
Dim UG As Worksheet 'Worksheet abbreviated reference BriefingAnalystUpsDwns
Dim RatingType As String 'The rating type/ category
Dim DataReigon As Range 'The final, semi- revised block of data that appears at the bottom of the sheet.
'Abbreviate worksheet name.
Set UGQ = Worksheets("BriefingAnalystUpsDwnsQuery")
Set UG = Worksheets("BriefingAnalystUpsDwns")
'Get final row of worksheet.
UGQFinalRow = UGQ.Cells(Rows.count, 1).End(xlUp).Row
UGQFinalRowFixed = UGQ.Cells(Rows.count, 1).End(xlUp).Row
'Initialize main outer loop.
Dim i As Integer 'Counter variable for main/ outer loop
Dim j As Integer 'Counter variable for inner do while loop
For i = 1 To UGQFinalRow 'Outer loop that loops through the entire worksheet looking for the word "company."
'Once it finds "company", it checks for "upgrades, downgrades, and coverage initiated."
If UGQ.Cells(i, 1).Value = "Company" Then
Select Case UGQ.Cells((i - 3), 1).Value
Case "Upgrades"
RatingType = "Upgrades"
Case "Downgrades"
RatingType = "Downgrades"
Case "Coverage Initiated"
RatingType = "Initiated"
Case "Coverage Reit/Price Tgt Changed*"
RatingType = "TargetChange"
End Select
j = 1
Do While UGQ.Cells(i + j, 1).Value <> ""
j = j + 1
Loop
ArrayLength = WorksheetFunction.count(DataArray)
ReDim Preserve DataArray(ArrayLength)
DataArray(i + j, 1) = UGQ.Cells(i + j, 2).Value 'Symbol load into array
DataArray(i + j, 2) = UGQ.Cells(i + j, 1).Value 'Company load into array
DataArray(i + j, 3) = RatingType 'Alert name load into array
DataArray(i + j, 4) = UGQ.Cells(i + j, 3).Value 'Firm load into array
DataArray(i + j, 5) = UGQ.Cells(i + j, 4).Value 'Rating change load into array
DataArray(i + j, 6) = UGQ.Cells(i + j, 5).Value 'Price target load into array
j = j - 2
End If
Next i
