ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Paste 2 arrays to 1 row (https://www.excelbanter.com/excel-programming/360332-paste-2-arrays-1-row.html)

Dale
Paste 2 arrays to 1 row
 
Im stumped.

The following code works just fine but is one step beyond what the macro
recorder could do and I would like to graduate to the next level of
programming in Excel. I have reviewed what is in the news groups and tried a
few but cant seem to modify the code to work for me. Any suggestions? Tks.

My code

'goto back to the GetReadyData Workbook

ActiveWindow.ActivateNext
Sheets("Data").Select
vCellAddressCount = 1
vBODC = 1
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select 'Down
vPDC = 1

If vPLCount = 0 Then
For i = 1 To 16 'Pastes all of the Business Office Section
ActiveCell = vBOD(vBODC)
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vBODC = vBODC + 1
Next i
ActiveCell = "No Parts"
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vPDC = vPDC + 1

vCurrentRow = ActiveCell.Row 'Inputs NOW() into X+Row()
vCurrentRow = "X" & vCurrentRow
Range(vCurrentRow).Select
ActiveCell = Now()

ActiveCell.Offset(1, 0).Range("A1").Select 'Down
Selection.End(xlToLeft).Select
vBODC = 1

End If

For Z = 1 To vPLCount
For i = 1 To 16 'Pastes all of the Business Office Section
ActiveCell = vBOD(vBODC)
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vBODC = vBODC + 1
Next i
' vPLICount = vRowCount / 2
For i = 1 To 6 'Pastes all of the Parts Items Section
If vPLICount 0 Then
ActiveCell = vPD(vPDC)
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vPDC = vPDC + 1
Else
ActiveCell = "No Parts"
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vPDC = vPDC + 1
End If
Next i

vCurrentRow = ActiveCell.Row 'Inputs NOW() into X+Row()
vCurrentRow = "X" & vCurrentRow
Range(vCurrentRow).Select
ActiveCell = Now()

ActiveCell.Offset(1, 0).Range("A1").Select 'Down
Selection.End(xlToLeft).Select
vBODC = 1
Next Z

Code I found which works but does not paste in a row but rather in a column

With Worksheets("Data")
Set NextCell = .Cells(.Rows.count, "A").End(xlUp).Offset(1, 0)
End With
NextCell.Resize(UBound(vBOD) - LBound(vBOD) + 1, 1).Value =
Application.Transpose(vBOD)

Tom Ogilvy
Paste 2 arrays to 1 row
 


With Worksheets("Data")
Set NextCell = .Cells(.Rows.count, "A").End(xlUp).Offset(1, 0)
End With
NextCell.Resize(1,UBound(vBOD) - LBound(vBOD) + 1).Value = vBOD

--
Regards,
Tom Ogilvy




"Dale" wrote:

Im stumped.

The following code works just fine but is one step beyond what the macro
recorder could do and I would like to graduate to the next level of
programming in Excel. I have reviewed what is in the news groups and tried a
few but cant seem to modify the code to work for me. Any suggestions? Tks.

My code

'goto back to the GetReadyData Workbook

ActiveWindow.ActivateNext
Sheets("Data").Select
vCellAddressCount = 1
vBODC = 1
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select 'Down
vPDC = 1

If vPLCount = 0 Then
For i = 1 To 16 'Pastes all of the Business Office Section
ActiveCell = vBOD(vBODC)
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vBODC = vBODC + 1
Next i
ActiveCell = "No Parts"
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vPDC = vPDC + 1

vCurrentRow = ActiveCell.Row 'Inputs NOW() into X+Row()
vCurrentRow = "X" & vCurrentRow
Range(vCurrentRow).Select
ActiveCell = Now()

ActiveCell.Offset(1, 0).Range("A1").Select 'Down
Selection.End(xlToLeft).Select
vBODC = 1

End If

For Z = 1 To vPLCount
For i = 1 To 16 'Pastes all of the Business Office Section
ActiveCell = vBOD(vBODC)
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vBODC = vBODC + 1
Next i
' vPLICount = vRowCount / 2
For i = 1 To 6 'Pastes all of the Parts Items Section
If vPLICount 0 Then
ActiveCell = vPD(vPDC)
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vPDC = vPDC + 1
Else
ActiveCell = "No Parts"
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vPDC = vPDC + 1
End If
Next i

vCurrentRow = ActiveCell.Row 'Inputs NOW() into X+Row()
vCurrentRow = "X" & vCurrentRow
Range(vCurrentRow).Select
ActiveCell = Now()

ActiveCell.Offset(1, 0).Range("A1").Select 'Down
Selection.End(xlToLeft).Select
vBODC = 1
Next Z

Code I found which works but does not paste in a row but rather in a column

With Worksheets("Data")
Set NextCell = .Cells(.Rows.count, "A").End(xlUp).Offset(1, 0)
End With
NextCell.Resize(UBound(vBOD) - LBound(vBOD) + 1, 1).Value =
Application.Transpose(vBOD)

Dale
Paste 2 arrays to 1 row
 
Excellent, It works, Any suggetions on how to paste both arrays on the same
row, vBOD & vPDC from my old code?

Dale

"Tom Ogilvy" wrote:



With Worksheets("Data")
Set NextCell = .Cells(.Rows.count, "A").End(xlUp).Offset(1, 0)
End With
NextCell.Resize(1,UBound(vBOD) - LBound(vBOD) + 1).Value = vBOD

--
Regards,
Tom Ogilvy




"Dale" wrote:

Im stumped.

The following code works just fine but is one step beyond what the macro
recorder could do and I would like to graduate to the next level of
programming in Excel. I have reviewed what is in the news groups and tried a
few but cant seem to modify the code to work for me. Any suggestions? Tks.

My code

'goto back to the GetReadyData Workbook

ActiveWindow.ActivateNext
Sheets("Data").Select
vCellAddressCount = 1
vBODC = 1
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select 'Down
vPDC = 1

If vPLCount = 0 Then
For i = 1 To 16 'Pastes all of the Business Office Section
ActiveCell = vBOD(vBODC)
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vBODC = vBODC + 1
Next i
ActiveCell = "No Parts"
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vPDC = vPDC + 1

vCurrentRow = ActiveCell.Row 'Inputs NOW() into X+Row()
vCurrentRow = "X" & vCurrentRow
Range(vCurrentRow).Select
ActiveCell = Now()

ActiveCell.Offset(1, 0).Range("A1").Select 'Down
Selection.End(xlToLeft).Select
vBODC = 1

End If

For Z = 1 To vPLCount
For i = 1 To 16 'Pastes all of the Business Office Section
ActiveCell = vBOD(vBODC)
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vBODC = vBODC + 1
Next i
' vPLICount = vRowCount / 2
For i = 1 To 6 'Pastes all of the Parts Items Section
If vPLICount 0 Then
ActiveCell = vPD(vPDC)
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vPDC = vPDC + 1
Else
ActiveCell = "No Parts"
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vPDC = vPDC + 1
End If
Next i

vCurrentRow = ActiveCell.Row 'Inputs NOW() into X+Row()
vCurrentRow = "X" & vCurrentRow
Range(vCurrentRow).Select
ActiveCell = Now()

ActiveCell.Offset(1, 0).Range("A1").Select 'Down
Selection.End(xlToLeft).Select
vBODC = 1
Next Z

Code I found which works but does not paste in a row but rather in a column

With Worksheets("Data")
Set NextCell = .Cells(.Rows.count, "A").End(xlUp).Offset(1, 0)
End With
NextCell.Resize(UBound(vBOD) - LBound(vBOD) + 1, 1).Value =
Application.Transpose(vBOD)

Tom Ogilvy
Paste 2 arrays to 1 row
 
I didn't wade through all you code in detail, but let me guess

With Worksheets("Data")
Set NextCell = .Cells(.Rows.count, "A").End(xlUp).Offset(1, 0)
End With
Dim L as Long
L = UBound(vBOD) - LBound(vBOD) + 1
NextCell.Resize(1,L).Value = vBOD
NextCell.offset(0,L).Resize(1, Ubound(vPDC) - Lbound(vPDC) + 1) = vPDC

Assuming they will both fit.

--
Regards,
Tom Ogilvy


"Dale" wrote in message
...
Excellent, It works, Any suggetions on how to paste both arrays on the
same
row, vBOD & vPDC from my old code?

Dale

"Tom Ogilvy" wrote:



With Worksheets("Data")
Set NextCell = .Cells(.Rows.count, "A").End(xlUp).Offset(1, 0)
End With
NextCell.Resize(1,UBound(vBOD) - LBound(vBOD) + 1).Value = vBOD

--
Regards,
Tom Ogilvy




"Dale" wrote:

I'm stumped.

The following code works just fine but is one step beyond what the
macro
recorder could do and I would like to graduate to the next level of
programming in Excel. I have reviewed what is in the news groups and
tried a
few but can't seem to modify the code to work for me. Any suggestions?
Tks.

My code

'goto back to the GetReadyData Workbook

ActiveWindow.ActivateNext
Sheets("Data").Select
vCellAddressCount = 1
vBODC = 1
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select 'Down
vPDC = 1

If vPLCount = 0 Then
For i = 1 To 16 'Pastes all of the Business Office Section
ActiveCell = vBOD(vBODC)
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vBODC = vBODC + 1
Next i
ActiveCell = "No Parts"
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vPDC = vPDC + 1

vCurrentRow = ActiveCell.Row 'Inputs NOW() into X+Row()
vCurrentRow = "X" & vCurrentRow
Range(vCurrentRow).Select
ActiveCell = Now()

ActiveCell.Offset(1, 0).Range("A1").Select 'Down
Selection.End(xlToLeft).Select
vBODC = 1

End If

For Z = 1 To vPLCount
For i = 1 To 16 'Pastes all of the Business Office Section
ActiveCell = vBOD(vBODC)
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vBODC = vBODC + 1
Next i
' vPLICount = vRowCount / 2
For i = 1 To 6 'Pastes all of the Parts Items Section
If vPLICount 0 Then
ActiveCell = vPD(vPDC)
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vPDC = vPDC + 1
Else
ActiveCell = "No Parts"
ActiveCell.Offset(0, 1).Range("A1").Select 'Right
vPDC = vPDC + 1
End If
Next i

vCurrentRow = ActiveCell.Row 'Inputs NOW() into X+Row()
vCurrentRow = "X" & vCurrentRow
Range(vCurrentRow).Select
ActiveCell = Now()

ActiveCell.Offset(1, 0).Range("A1").Select 'Down
Selection.End(xlToLeft).Select
vBODC = 1
Next Z

Code I found which works but does not paste in a row but rather in a
column

With Worksheets("Data")
Set NextCell = .Cells(.Rows.count, "A").End(xlUp).Offset(1, 0)
End With
NextCell.Resize(UBound(vBOD) - LBound(vBOD) + 1, 1).Value =
Application.Transpose(vBOD)


All times are GMT +1. The time now is 01:25 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com