JBC
06-16-2015, 06:00 AM
Hi All,
I am self learning VBA with varying levels of success but for the last two days I have met a hurdle that I cannot beat.
My current code:
Private Sub CommandButton1_Click()
Dim r As Integer
Dim nRow As Integer
Dim nColumn As Integer
For r = 8 To 246
If Cells(r, 2).Value > 0 Then
nRow = (Cells(r, 2).Value + 2)
nColumn = Cells(3, 1).Value
Cells(r, 1).Select
Selection.Copy
Sheets("Sheet1").Select
Cells(nRow, nColumn).Select
Selection.Paste
Sheets("00").Select
End If
Next r
End Sub
It is probably very messy to a trained eye but what I am trying to achieve is the following:
Cells b8:b246 on sheet "00" contain a number 1-99 I would like to use this number (+2) as the row reference. (nRow)
Cell A3 on the same sheet contains a number that I want to use as the Column reference. (nColumn)
The references are eventually to be used in a different workbook but at the moment I am trying to transfer the data in the corresponding A(column) to another sheet "Sheet1" on the same workbook in the cell with the above references.
5
Drop
Address
20
PL 4419
Workday, Finsbury Circus House, 10 South Place
W PL 4419
1
F Spitz
Is a small snapshot of the sheet - and in this example "W PL 4419" is to be copied and pasted into cell(3,5) (or E3) on the other sheet. Currently the code breaks down at the point highlighted. Although hovering the mouse over nRow gives the correct figure (3) and nColumn gives the correct figure of (5) if I change r from 8 to 9 and run again nRow and nColumn change to what they are meant to but it just won't work as macro.
If anyone can tell me where I am going wrong I would be most gracious.
I am self learning VBA with varying levels of success but for the last two days I have met a hurdle that I cannot beat.
My current code:
Private Sub CommandButton1_Click()
Dim r As Integer
Dim nRow As Integer
Dim nColumn As Integer
For r = 8 To 246
If Cells(r, 2).Value > 0 Then
nRow = (Cells(r, 2).Value + 2)
nColumn = Cells(3, 1).Value
Cells(r, 1).Select
Selection.Copy
Sheets("Sheet1").Select
Cells(nRow, nColumn).Select
Selection.Paste
Sheets("00").Select
End If
Next r
End Sub
It is probably very messy to a trained eye but what I am trying to achieve is the following:
Cells b8:b246 on sheet "00" contain a number 1-99 I would like to use this number (+2) as the row reference. (nRow)
Cell A3 on the same sheet contains a number that I want to use as the Column reference. (nColumn)
The references are eventually to be used in a different workbook but at the moment I am trying to transfer the data in the corresponding A(column) to another sheet "Sheet1" on the same workbook in the cell with the above references.
5
Drop
Address
20
PL 4419
Workday, Finsbury Circus House, 10 South Place
W PL 4419
1
F Spitz
Is a small snapshot of the sheet - and in this example "W PL 4419" is to be copied and pasted into cell(3,5) (or E3) on the other sheet. Currently the code breaks down at the point highlighted. Although hovering the mouse over nRow gives the correct figure (3) and nColumn gives the correct figure of (5) if I change r from 8 to 9 and run again nRow and nColumn change to what they are meant to but it just won't work as macro.
If anyone can tell me where I am going wrong I would be most gracious.