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>
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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]
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,545
Messages
5,511,951
Members
408,871
Latest member
Usman21

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top