Macro to lookup date, name and place data into it

ExcelRytis

New Member
Joined
Dec 22, 2017
Messages
13
Good morning all of you,

This is my first ever post in this forum and I am sincerely stuck on this on for the last week...

I am looking for a macro to lookup cells B2 and B3 in calculator sheet which are name and date and cross referencing in a productivity spreadsheet determine the cross cell into which it needs to paste the data and actually paste it as value.

Any ideas?

date1date2date3date4
name1
name2
name3
name4Cross referenced

<tbody>
</tbody>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

ExcelRytis

New Member
Joined
Dec 22, 2017
Messages
13
Now i finally found how to return the cell address that the data needs to go to, how do i get VBA to paste it into that address?

formula to get address: =CELL("address",INDEX(Productivity!A4:JA73,MATCH(C5,Productivity!A4:A97,0),MATCH(B5,Productivity!A4:JA4,0)))
 

ExcelRytis

New Member
Joined
Dec 22, 2017
Messages
13
I'm looking at a VBA code something like
Code:
Application.Goto Reference:=Range("LocationToPasteTo.Value")

but it does not seem to like that... Please someone help..
 

ExcelRytis

New Member
Joined
Dec 22, 2017
Messages
13
Found this code, but i don't know how to implement it correctly......
Code:
[COLOR=#333333]Public Sub TesterA01()[/COLOR]
[COLOR=#333333]Dim SH As Worksheet[/COLOR]
[COLOR=#333333]Dim arr As Variant[/COLOR]
[COLOR=#333333]Dim rng As Range[/COLOR]

[COLOR=#333333]arr = Split(ActiveCell.Value, "!")[/COLOR]

[COLOR=#333333]Set SH = Sheets(arr(0))[/COLOR]
[COLOR=#333333]Set rng = SH.Range(arr(1))[/COLOR]

[COLOR=#333333]Application.Goto Reference:=rng[/COLOR]

[COLOR=#333333]End Sub[/COLOR]
 

Watch MrExcel Video

Forum statistics

Threads
1,133,040
Messages
5,656,531
Members
418,313
Latest member
TrinityGal

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
Top