Posts by cdemaria

Re: VBA loop through array but skipping blank cells

Thanks for the suggestion NoSparks, but I cant rely on the cells remaining empty. There will be multiple (30+) versions of this workbook, so if a user puts something in an empty cell, intentional or not, the data will be unreliable.

I have a single column array I'm spinning through to retrieve data, but I have to skip every 4th cell, which is empty. There is the possibility that a user could put a note or something in those cells, so I cant absolutely count on them always being empty. Is there a more elegant way to skip these cells than what I have below?

For x = 1 To y  
    
        If (x Mod 4) = 0 Then
            GoTo SkipSpace
        End If
        
        ' Get cell data...
    
SkipSpace:
    Next

Thanks for any ideas!

Re: Count Unique Cell Values

I ended up writing a VBA loop, but if anyone has a smarter idea, I'm all ears. Thanks!

Hi All,
Ive searched the forum but cant seem to find specifically what I'm looking for. I need to count the number of unique values within a range and also return that unique value with its total count. I would not know in advance what the values are.

aaa
aaa
aaa
bbb
ccc
ccc
ccc
ccc
ccc

Desired results:
aaa 3
bbb 1
ccc 5

I cant visualize what type of code would accomplish this. Can this be done with a modified sumproduct formula, or does it need to be inserted as a VBA function?
Thanks in advance.

Re: Loop Down Column & Across Rows

I need to obtain data from multiple cells that are related.
For example, say column A are peoples names.
Across the rows next to them are the cars they own, which varies depending upon the owner.
I need to retrieve a persons name and then go thru each car they own, 1 by 1.
So start point 1 is the first name, start point 2 is cell B2, the first car. I figure if I do a COUNTA on the start2 range in each row, I can use that variable in the macro since that number will vary.

Re: Loop Down Column & Across Rows

Thanks for the reply, but I dont think this is what I'm looking for, as I think I need 2 ranges. The 'start' range represents column A row 1, which will work downwards. A second range would need to begin in column B, row 1 and work across until reaching a blank cell, then the first range would go to cell A 2, etc.

Re: Vb Loop Within A Loop

Sicarii,
I guess that could be done also, but the issue I'm having is how to create a second loop that runs through each column that contains data as it spins downward.
Hope I am making sense.
Chris

I need to create a loop within a loop. First loop works fine as follows:
-------------------------------

x = 0
Dim DirArray(0 To 80) As String
Dim intI As Integer
For intI = 0 To 80
DirArray(intI) = Range("start").Offset(intI, 0).Value
Next


For x = 0 To 80
lob = DirArray(x)
Range("lob") = lob
Calculate
Sheets("WBB LOB Summary").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next

----------------------------
At the 'start' point, first loop works downward through rows. Second loop would work across columns in each row within the first loop. I will have to use a variable for 'y' (the across value) b/c the number of cells containing data can vary between 3 & 9, but I have a COUNTA in each row setup to use as the variable.

So spreadsheet data would look like this with A being the 'start' cell:
A 1 2 3
B 1 2 3 4 5 6
C 1 2 3 4

I need to pickoff A1, A2, A3, B1, etc, picking up every sub-category on the sheet.
Thanks in advance!
Chris