1

I'm working on a project for work and I need help changing this code to work with my current worksheet. In my "Registration" worksheet I have an area where I'm trying to type in the "Athlete" name and it will pull the information from the "Master List" worksheet.

Update Here is the original workbook that this coding is coming from. I'm trying to get this coding to work on my new work book because I'm using two worksheets instead of one.

Original Workbook

Here is the old code that I have but am unable to configure it to work with this new workbook since I'm working off of two worksheets instead of one.

Sub Update() Dim rngSearch As Range, rngFound As Range Set rngSearch = Range("B18:B150") Set rngFound = rngSearch.Find(What:=Range("B12").Value, LookIn:=xlValues, LookAt:=xlPart) If rngFound Is Nothing Then MsgBox "Please recheck name" Range("B12").Value = " " Exit Sub End If 'Range("B12:O12").Copy 'Range("B" & rngFound.Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Range("B12:O12").ClearContents For a = 5 To 14 If Cells(12, a) = "X" Or Cells(12, a) = "X" Then Cells(rngFound.Row, a).Value = "X" Else Cells(rngFound.Row, a).Value = " " End If Next Range("B12:O12").ClearContents Range("C12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,2,FALSE))" Range("D12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,3,FALSE))" Range("E12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,4,FALSE))" Range("F12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,5,FALSE))" Range("G12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,6,FALSE))" Range("H12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,7,FALSE))" Range("I12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,8,FALSE))" Range("J12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,9,FALSE))" Range("K12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,10,FALSE))" Range("L12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,11,FALSE))" Range("M12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,12,FALSE))" Range("N12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,13,FALSE))" Range("O12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,14,FALSE))" End Sub

* Update * I was able to find a Macro that needs to be updated as well. This was hiding in a different Module.

Sub values() ' 'values Macro ' ' Range("B12:O12").Select Selection.Copy Range("B18").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub

Here are a couple images of the new worksheets I am working on.

Update Athlete: A29:O29

"Master List"

Improve this question
6
  • 2
    Please don't post images of code. It might help to read How to Ask. Commented Aug 16, 2017 at 0:15
  • 1
    You should attach your code here instead of taking a screenshot like that. Commented Aug 16, 2017 at 0:19
  • 1
    @John Coleman: I tried to copy and paste the code, but there was a formatting error i was getting when I tried to post the code. That's why I screenshot it and posted it that way. Commented Aug 16, 2017 at 0:20
  • 4
    Copy/paste the code into the edit window, highlight the code, and then hit Ctrl+K? In general, people on Stack Overflow hate images of code. Nobody is going to bother to type the code, so why bother to even try to answer it? You can't experiment with it without undue effort. Commented Aug 16, 2017 at 0:21
  • @John Coleman: Thank you, I am new to this forum site and I am still learning. I was able to figure it out. Commented Aug 16, 2017 at 0:28

1 Answer 1

Reset to default
1

In your get data file (the one with button) try remove any merge column (A&B). Do the same for "master list" As you might seen, you input name in cell A29 so you need to change B12 -> A29 

Sub Update() Dim rngSearch As Range, rngFound As Range 'Search range: Change sheets and range Set rngSearch = Sheets("Master list").Range("A3:O150") ' You type in cell A29 then search by cell A29, not B12 Set rngFound = rngSearch.Find(What:=Range("A29").Value, LookIn:=xlValues, LookAt:=xlPart) If rngFound Is Nothing Then MsgBox "Please recheck name" Range("A29").Value = " " Exit Sub End If 'I think this is meaningless 'Range("B12:O12").Copy 'Range("B" & rngFound.Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Range("B12:O12").ClearContents 'For a = 5 To 14 'If Cells(12, a) = "X" Or Cells(12, a) = "X" Then 'Cells(rngFound.Row, a).Value = "X" 'Else 'Cells(rngFound.Row, a).Value = " " 'End If 'Next 'Range("B12:O12").ClearContents 'You should pay attention to this as this is where your data will show: Row 29 from column B to O ( I assume that you remove the merge cell A&B) ' Source :master list Range("B29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,2,FALSE))" Range("C29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,3,FALSE))" Range("D29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,4,FALSE))" Range("E29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,5,FALSE))" Range("F29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,6,FALSE))" Range("G29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,7,FALSE))" Range("H29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,8,FALSE))" Range("I29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,9,FALSE))" Range("J29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,10,FALSE))" Range("K29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,11,FALSE))" Range("L29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,12,FALSE))" Range("M29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,13,FALSE))" Range("N29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,14,FALSE))" End Sub

update for the values sub

 Sub values() ' 'values Macro ' ' Edit your target sheet here: With Sheets("target sheet name") ' Avoid select, it will slow down your code .Range("B12:O12").Copy .Range("B18").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False end with End Sub

p/s: might be this one is all you need:

=IFERROR(VLOOKUP(A29,'Master list'!$A3:$O150,2,FALSE),"")
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

updated answer as you finally able to paste your code.
Thank you for your help. I was able to find a Macro that would need to be corrected. Would you be able to look at it and let me know how it could be changed. Thank you very much.
Without knowing where and purpose of that function(context), it's hard to give you any recommendation. Btw, you could try to modify those "Range" with a parent sheet object like "Sheets("Master List").Range("B12:O12")" . Be careful, it's values procedure, not value method which belongs to Range object.
All this coding was from a one worksheet. I'm trying to re-code it to work in a two worksheet coding. I'll see if I can upload an image of the original workbook this coding was coming from. Thanks again
Thank you for your time and help. I tried entering the code with nothing happening. It might be me not inputting it in correctly. It's also harder to explain than actually seeing. Thanks again.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.