choubix
06-22-2008, 10:54 AM
hello,
I was trying to do something not using a loop. Xld was kind enough to give me a code but it will take me some time to understand :( (thanks xld, I am going to take some time to go through the code to understand it)
Since I need to fix my spreadsheet Asap I came up with this dirty fix (using a loop...)
Sub Skip_Blanks_Simple()
Dim cCount As Variant
Dim i As Integer, j As Integer
cCount = Evaluate("SUMPRODUCT((Len(C1:C65000) > 0) * 1)")
For i = 1 To 88 'wsOutput.Range("B65536").End(xlUp).Count 88 wsOutput.Range(Cells(65535, 2)).End(xlUp).count
If Not IsEmpty(wsOutput.Cells(i, 3)) Then
For j = 1 To cCount
wsOutput.Cells(j, 6).Offset(, 1).Resize(, 2) = "= " & wsOutput.Cells(i, 3).Offset(, -1).Resize(, 2).Address & ""
'wsOutput.Cells(j, 5) = "=" & wsOutput.Cells(i, 2).Address & ""
'wsOutput.Cells(j, 6) = "=" & wsOutput.Cells(i, 3).Address & ""
Next
End If
Next
the idea is: I have a set of data in column B.
I use a loop in column B.
if for any value in column B their is a value in column C
in column E and F: copy the address of B an C (so output should be "=B22" and =C22) for instance)
right now I have 3 problems:
- in the 1st loop: I dont point correctly to the last cell in the range (so I had to hardcode the last cell for the sake of the example
- 2nd: the values returned are not correct using offset and resize
- 3rd: if I use the code that is turned off (
'wsOutput.Cells(j, 5) = "=" & wsOutput.Cells(i, 2).Address & ""
'wsOutput.Cells(j, 6) = "=" & wsOutput.Cells(i, 3).Address & "") it shows the correct data BUT it shows only the last data set (so the loop is not correct)
any idea what is wrong here please? (appart from the coder I currently am ;) )
thanks!
I was trying to do something not using a loop. Xld was kind enough to give me a code but it will take me some time to understand :( (thanks xld, I am going to take some time to go through the code to understand it)
Since I need to fix my spreadsheet Asap I came up with this dirty fix (using a loop...)
Sub Skip_Blanks_Simple()
Dim cCount As Variant
Dim i As Integer, j As Integer
cCount = Evaluate("SUMPRODUCT((Len(C1:C65000) > 0) * 1)")
For i = 1 To 88 'wsOutput.Range("B65536").End(xlUp).Count 88 wsOutput.Range(Cells(65535, 2)).End(xlUp).count
If Not IsEmpty(wsOutput.Cells(i, 3)) Then
For j = 1 To cCount
wsOutput.Cells(j, 6).Offset(, 1).Resize(, 2) = "= " & wsOutput.Cells(i, 3).Offset(, -1).Resize(, 2).Address & ""
'wsOutput.Cells(j, 5) = "=" & wsOutput.Cells(i, 2).Address & ""
'wsOutput.Cells(j, 6) = "=" & wsOutput.Cells(i, 3).Address & ""
Next
End If
Next
the idea is: I have a set of data in column B.
I use a loop in column B.
if for any value in column B their is a value in column C
in column E and F: copy the address of B an C (so output should be "=B22" and =C22) for instance)
right now I have 3 problems:
- in the 1st loop: I dont point correctly to the last cell in the range (so I had to hardcode the last cell for the sake of the example
- 2nd: the values returned are not correct using offset and resize
- 3rd: if I use the code that is turned off (
'wsOutput.Cells(j, 5) = "=" & wsOutput.Cells(i, 2).Address & ""
'wsOutput.Cells(j, 6) = "=" & wsOutput.Cells(i, 3).Address & "") it shows the correct data BUT it shows only the last data set (so the loop is not correct)
any idea what is wrong here please? (appart from the coder I currently am ;) )
thanks!