Overlapping of Text VBA Problem

[keepers123]

Hi,

I am having a little problem.

Problem: I am calling a sub function multiple times which places data extracted from a data cube into a excel sheet. The problem it writes over the same cells that is pastes them in. Below is the sub function i am calling multiple times. I need the data to be below each other as i keep calling it.



[VBA]For r = 1 To count
If arr(r, 2) = "Total" Then
counter = counter + 1
ThisWorkbook.Sheets("Sheet3").Range("C" & (counter + 9)).Value = arr(r, 1)
ThisWorkbook.Sheets("Sheet3").Range("D" & (counter + 9)).Value = arr(r, 13)
ThisWorkbook.Sheets("Sheet3").Range("E" & (counter + 9)).Value = arr(r, 15)
ThisWorkbook.Sheets("Sheet3").Range("F" & (counter + 9)).Value = arr(r, 16)
ThisWorkbook.Sheets("Sheet3").Range("G" & (counter + 9)).Value = arr(r, 19)
End If
Next
[/VBA]

[JKwan]

should you not add the below line after each time that you output to the sheet? Otherwise, you are overwriting the content.
[VBA]counter = counter + 1[/VBA]

[SamT]

[vba]With ThisWorkbook.Sheets("Sheet3") 'Now you don't have to type this over and over
'Set counter to last used row + 1
Counter = .Cells(Rows.Count, 3).End(xlUp).Row + 1
For r = 1 To Count
If arr(r, 2) = "Total" Then
Counter = Counter + 1
.Range("C" & (Counter + 9)).Value = arr(r, 1)
.Range("D" & (Counter + 9)).Value = arr(r, 13)
.Range("E" & (Counter + 9)).Value = arr(r, 15)
.Range("F" & (Counter + 9)).Value = arr(r, 16)
.Range("G" & (Counter + 9)).Value = arr(r, 19)
End If
Next
End With
[/vba]

You might want to delete those + 9 's after the Counter's

[mikerickson]

[VBA]With ThisWorkbook.Sheets("Sheet3")
For r = 1 To Count
If arr(r, 2) = "Total" Then
With .Cells(.Rows.Count, 3).End(xlup).Offset(1,0).EntireRow
.Range("C1") = arr(r,1)
.Range("D1") = arr(r,13)
.Range("E1") = arr(r,15)
.Range("F1") = arr(r,16)
.Range("G1") = arr(r,19)
End With
End If
Next
End With [/VBA]

[snb]

or at last, only:


[VBA]
For r = 1 To Count
If arr(r, 2) = "Total" Then ThisWorkbook.Sheets("Sheet3").Cells(Rows.Count, 3).End(xlup).Offset(1).resize(,5)=array(arr(r,1),arr(r,13),arr(r,15),arr(r, 16),arr(r,19))
Next
[/VBA]