Excel Load Data button

Question

I have a work sheet (Stored Jobs) that I have a macro that will save all the jobs data to. I am now attempting to make a macro that will reload that saved data Based On the job number entered into F9 on the MHBD work sheet the job number sits in column B. I need f to be returned as the row number that the job sits on so that I can copy data from that row into different cells on the MHBD sheet. I cant just copy the row straight because the data on the MHBD sheet is not in a row its scattered. How do I get it to return the row that the job lives on and use that to copy cells off of that row?

Sub Load_Button()

Dim StoredJobs As Worksheet
Dim MHBD As Worksheet
Dim lastRow As Long
Dim f As Range, theJob

Set StoredJobs = Worksheets("Stored Jobs")
Set MHBD = Worksheets("Manual Hour Break Down")
lastRow = StoredJobs.Range("B" & Rows.Count).End(xlUp).Row

theJob = MHBD.Range("F9").Value

'find the current job if it exists
Set f = StoredJobs.Range("B4:B" & lastRow).Find(what:=theJob, lookat:=xlWhole).Row

'if not found, use the next empty row
If f Is Nothing Then MsgBox "Job has not been entered."

End Sub

"what am i doing wrong so far?" You need to tell us. If this code has a bug, describe the bug. — John Coleman
You have declared lastRow but I can't see anywhere in your code where you are assigning a value to the variable. I agree with @JohnColeman, just asking us what you doing wrong without telling us what is going wrong makes it very difficult to understand your issue. Saying that, I suspect that lastRow is one of your issues — Zac
The only error i am getting is "Compile Erro: type mismatch" for .row I also updated code for lastrow issue — jparks54
Maybe you should put StoredJobs in front of Rows.Count in the lastRow variable. ie. lastRow=StoredJobs.Range("B" & StoredJobs.Rows.Count).End(xlUp).Row — ExcelinEfendisi
.Row is an integer. It doesn't make sense to set f (which is a range) equal to this number. Perhaps you need to drop .Row from that line. — John Coleman

jparks54 jparks54 · Accepted Answer · 2017-09-08T18:24:11

Got it. @johnColeman you were correct on removing .Row

Thanks everyone else for the ideas and help.

Sub Load_Button()

Dim StoredJobs As Worksheet
Dim MHBD As Worksheet
Dim lastRow As Long
Dim f As Range, theJob

Set StoredJobs = Worksheets("Stored Jobs")
Set MHBD = Worksheets("Manual Hours Break Down")
lastRow = StoredJobs.Range("B" & Rows.Count).End(xlUp).Row

theJob = MHBD.Range("F9").Value

'find the current job if it exists
Set f = StoredJobs.Range("B4:B" & lastRow).Find(what:=theJob, lookat:=xlWhole)

'if not found, error message
If f Is Nothing Then
    MsgBox "Job: " & MHBD.Range("F9").Value & " has not been entered" & vbNewLine & "Try the Quote Number"

'if found, copy data
Else
    StoredJobs.Range("C" & (f.Row)).Copy
    MHBD.Range("B3").PasteSpecial xlPasteValues

End If

End Sub

Excel Load Data button

1 Answers