Referencing location based on Cell data

[JBC]

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.

[Kenneth Hobs]

Welcome to the forum!

When pasting code, click the # icon in a replies toolbar to insert code tags and paste between them. This keeps your structure.

Be sure to set the Dim type for row variables to Long.

I may not fully understand what you need. Here is a stab at it.

Private Sub CommandButton1_Click()
Dim r As Long, nRow As Long, nColumn As Integer
For r = 8 To 246
With Worksheets("00")
If .Cells(r, 2).Value > 0 Then
nRow = .Cells(r, 2).Value + 2
nColumn = .Cells(3, 1).Value
.Cells(r, 1).Copy Sheets("Sheet1").Cells(nRow, nColumn)
End If
End With
Next r

Application.CutCopyMode = False
End Sub
You can attach simple example files by clicking the Go Advanced button in lower right of a reply and then the paperclip icon on the toolbar.

[JBC]

You don't even know what I am trying to do and you succeed!! With a little manipulation of the sheet layout the above works fine. The only problem this leaves me is that it doesn't PasteValue it pastes with format as well.

Amazing pieces of advice. Thank you.

[Kenneth Hobs]

Then use Copy and then use PasteSpecial. You can record the macro to do that to get help with the syntax or ask. Another approach if you just want value is:

Replace the Copy line with:

Sheets("Sheet1").Cells(nRow, nColumn).Value = .Cells(r, 1).Value)

[JBC]

You, sir, are amazing. Massive thank you.