PDA

View Full Version : Overlapping of Text VBA Problem



keepers123
05-29-2013, 02:56 PM
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.



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

JKwan
05-29-2013, 04:01 PM
should you not add the below line after each time that you output to the sheet? Otherwise, you are overwriting the content.
counter = counter + 1

SamT
05-29-2013, 04:43 PM
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


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

mikerickson
05-30-2013, 08:57 PM
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

snb
05-31-2013, 06:14 AM
or at last, only:



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