VBA: Paste in Column and Row containing specific value.

LWell

New Member
Joined
Jun 1, 2015
Messages
20
Hello,

I have a workbook with two sheets. One sheet (Sheet1) looks like this:

Jan.Feb.Mar.Apr.MayJun.Jul.Aug.Sep.Oct.Nov.Dec.
1
2
3
4
5
6
etc

<tbody>
</tbody>

The other (Sheet2) looks like this
1January$500.84
27January$233.10
3March$87.25
7April$442.70
etc.etc.etc.

<tbody>
</tbody>

I want to post the values in Sheet2 column D into their respective place in Sheet1. I am using the following code to copy the data:

Code:
Set Rng = Range("C1:C20000")
    
        For Each rCell In Rng.Cells
    
        If rCell.Value = "January" Then
            rCell.Select
            ActiveCell.Offset(, 1).Copy 
        Exit For
        End If
        Next rCell

I have one of those for each month.

I am looking for a pasting code that will be something like "paste in column that contains value that is offset ,-1" and "paste in row that contains value that is offset ,-3"

I hope that makes sense. Thank you guys for any help.

Best,
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sub moveValues()
For i = 1 To Range("A10000").End(xlUp).Row
mo = Left(Sheet2.Cells(i, 3).Value, 3) & "."
cl = Application.Match(mo, Sheet1.Rows(1), 0)
rw = Sheet2.Cells(i, 1).Value + 1
Sheet1.Cells(rw, cl).Value = Sheet2.Cells(i, 4).Value
Next
End Sub
 
Upvote 0
Works like a charm; thank you! Hypothetically, how would this work if Sheet2 column A was not a number but instead a name? Would you simply Application.Match for the row as well?
 
Upvote 0

Forum statistics

Threads
1,196,324
Messages
6,014,645
Members
441,834
Latest member
GHOSTOF309

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top