Application or object-defined error 1004

[juliet_jia]

Hi everybody,

I'm trying to loop through a column which consists of blocks of empty and text cells. I need to get the first and last cell of each blocks which have values in the cells. The (simplified) macro looks something like this:


For i = 1 To j
x(1) = 2
x(i) = Cells(x(i), 22).End(xlDown).row
Next i

But I get application or object-defined error. Can anyone tells what's wrong?

Thanks

 

[Rick Rothstein]

The following code will iterate through all the blocks of data... your code for each block would go inside the For..Next loop and use the Range variable A to reference each block of text. My code calculates the first and last cell address (as you requested) and displays them in a MessagBox.

Sub DemoGetAreasOfData()
  Dim A As Range, FirstCell As String, LastCell As String, Addresses() As String
  Const DataColumn As String = "A"
  For Each A In Columns(DataColumn).SpecialCells(xlCellTypeConstants).Areas
    Addresses = Split(A.Address(0, 0) & ":" & A.Address(0, 0), ":")
    FirstCell = Addresses(0)
    LastCell = Addresses(1)
    MsgBox "First Cell Address: " & FirstCell & vbLf & "Last Cell Address: " & LastCell
  Next
End Sub

 

[juliet_jia]

Thanks Rick! Your way does give me the addresses....
But I'd like to use the way I had, if possible of course. As I need to get the FirstCell and LastCell value, plus the corresponding rows in other columns...also my mind works better with arrays.

For m = 1 To n
    
    For i = 1 To j
        x(1) = 2
        x(i) = Cells(x(i), 22).End(xlDown).row
        
        If x(i) <> "singapore" Then
            If x(i) Mod 2 = 0 Then
                v_port(m) = Cells(x(i), 22).Text  'these are the FirstCell in the text range
                v_TA(m) = Cells(x(i), 4).Text
            Else
                V_TD(m) = Cells(x(i), 4).Text
            End If
            
        End If

   Next i
Next m

Any way to make this work?
Many thanks!

 

[Rick Rothstein]

I could try to do it your way, but I think the approach might be more cumbersome. However, it order to even try, you will have to describe what your variable mean (their names are not very descriptive) and, in the case of n and j, what they are set to initially.

 

[juliet_jia]

to describe what your variable mean

I'm not so good at Dim variables specifically, anyway:

Dim i, j, m, n as integer
Dim x(), v_port(), v_ta(), v_tb() as variant


j = worksheets("sheet1").range("row_count").count
n= 100

meanwhile, I'm trying to implement your way in my code.....

 

[juliet_jia]

Hi Rick, in your way, if I want to start FirstCell From A2, rather than A1, how should I do it?

 

[Rick Rothstein]

I'm not so good at Dim variables specifically

I didn't mean for you to show me your Dim statement, I meant for you to tell me the physical significance of the various variable. I have no idea what v_port or v_ta() is storing or why you are putting whatever into it. For instance, which is holding the first cell address, last cell address, etc.

Dim i, j, m, n as integer

But, as long as you did show me your code, the above Dim statement is not doing what I think you think it is doing. My guess is you think i, j, m and n are all being declared as Integers. They are not... only n is declared as an Integer, all the rest get declared as Variants. In VB, you must declare the data type for each variable individually. So, that Dim statement should read...

Dim i As Integer, j As Integer, m As Integer, n As Integer

 

[juliet_jia]

OK, I try to describe it....

The excel data consists, e.g.

V2:V10 empty V11:V15 = "New York", V16:V20 empty, V21:V39 = "London"...... 'note the height of the blocks is random

Column D: date+time

My goal is to get the contents in cells, in this case, V11,V15, V21, V39 and the corresponding timing in D11, D15, D21, D39.

In my code:
v_port(m) = V11,V15, V21, V39....
v_TA(m) = D11, D21.... i.e. Cells(FirstCell.Row,4)
v_TD(m) = D15, D39..... i.e. Cells(LastCell.Row,4)

so on.... I also need to pull out other corresponding data, some has to use if...then...

x(i) is an intermediate used as the row numbers...

Hope this is clear!

Also, thx for pointing out DIM