Trying to Learn VBA (Excel)

[Drumin_Phreak]

I am sorry if this has been explained, but I have searched extensively for this to be answered. However, let me first explain what I am trying to do:
I am attempting to take a value from the working workbook (workbook "A") check and see if the value exists in the validating workbook (workbook "B") and then copying the corresponding values (all on the same row) from workbook "B" to workbook "A". If the value is not found a message box is displayed "value cannot be found".
Having said all that, I am really asking for someone to explain to me what part of the code is what. I am not here to have someone to do my work for me, rather just help me come to understand what does what. I am here to learn; usually I am very good at deciphering and modifying code. This has me stumped! so without further ado
I have this code that i found and i think with some modifications it can work:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Dim MyPath, MyFile, MySheet, MyCell1, MyCell2, MyCell3, MyCell4 ' worksheet
Dim R, C ' row, column

Sub TestGetValue1()
MyPath = "Path to validating workbook" ' path
MyFile = "Validation.xls" ' file
MySheet = "02" ' sheet
MyCell1 = "B2920" ' range
MyCell2 = "I2920" ' range
MyCell3 = "J2920" ' range
MyCell4 = "K2920" ' range
'------------------------------------------------------------------------
Cells(ActiveCell.Row, 5).Value = GetValue(MyPath, MyFile, MySheet, MyCell2)
Cells(ActiveCell.Row, 16).Value = GetValue(MyPath, MyFile, MySheet, MyCell3)
Cells(ActiveCell.Row, 17).Value = GetValue(MyPath, MyFile, MySheet, MyCell4)
'------------------------------------------------------------------------
End Sub


Private Function GetValue(Fpath, Ffile, Fsheet, Fref)
Dim XL4macro As String
'- Excel 4 macro string (requires R1C1 reference)
XL4macro = "'" & Fpath & "[" & Ffile & "]" & Fsheet & "'!" & _
Range(Fref).Address(ReferenceStyle:=xlR1C1)
'-------------------------------------------------------------------------
'-Run the macro
GetValue = ExecuteExcel4Macro(XL4macro)
End Function</code>

 

[footoo]

Unless you have Excel 4, I recommend that you don't bother trying to learn XL4 Macros. It is unlikely that you will ever need to use.
What want to do can be done with VBA.

(A help file for XLM Macros might be available from the Microsoft web site.)

 

[Drumin_Phreak]

Footoo,

Thank you for the quick reply! Would you happen to know how I could accomplish this feat?

 

[footoo]

I am attempting to take a value from the working workbook (workbook "A") check and see if the value exists in the validating workbook (workbook "B") and then copying the corresponding values (all on the same row) from workbook "B" to workbook "A". If the value is not found a message box is displayed "value cannot be found".

Provide specific details (columns, rows, sheets, etc.)

 

[footoo]

Here's a link that explains the code you posted:
Excel Tips From John Walkenbach: A VBA Function To Get A Value From A Closed File

 

[Drumin_Phreak]

Sorry, I just went home when you replied to me last night.

Provide specific details (columns, rows, sheets, etc.)

Sure thing!

So what I have is a reporting system for a factory. The operator will fill in his information, and machine number. (See first image)

Based on his input for machine #, will determine what sheet in the external workbook the validation will come from. I have an external workbook (schedules) that has worksheets named according to machine number (01,02,03,04,...ect).

After the operator fills out that information, they then have to tell which schedule they will be working on. So they would then populate the "Schedule #" field:

Upon populating the field with the Schedule # (Column C in report) (Column B in Validation.xls), what I would like is for the Schedule # to be searched for within "C:/external/Validation.xls" and if it is found it will fill out the "PM Name (Columns D,E,F Merged in the report) (Column I in Validation.xls) "Length of rolls" (Column P) (Column J in Validation.xls) and the "Width of rolls" (Column Q) (Column K in Validation.xls) on the report with the information in the same row as the schedule # that was found. If the Schedule is not found it displays a popup message box that the schedule number does not exists. For better clarification on what the Validating work sheet looks like please see the image below.

Also, it should be noted each report could have several schedules on it (depending on how much the operator gets done in a day).

I appreciate the help and I hope I was able to explain it in a way that makes sense.

 

[Drumin_Phreak]

I have an external workbook (schedules) that has worksheets named according to machine number (01,02,03,04,...ect).

I appreciate the help and I hope I was able to explain it in a way that makes sense.

I do apologies the external Workbook is (Validation.xls) not Schedules.

Note: sorry for the second consecutive post, but i had no way to edit my original!

 

[footoo]

So what I have is a reporting system for a factory. The operator will fill in his information, and machine number. (See first image)

Based on his input for machine #, will determine what sheet in the external workbook the validation will come from. I have an external workbook (schedules) that has worksheets named according to machine number (01,02,03,04,...ect).

After the operator fills out that information, they then have to tell which schedule they will be working on. So they would then populate the "Schedule #" field:

Where is the Machine # input (what cell ref) ?
What is the first data row ?
Are both images above on the same worksheet?

 

[Drumin_Phreak]

Where is the Machine # input (what cell ref) ?
What is the first data row ?
Are both images above on the same worksheet?

Yes, both images are on the same worksheet (Report)

Machine # is Cell E2 within the "Report" Worksheet

The first data row for (Report) starts at A6

I should also note, what i like about the above code is it opens up the (Validation) sheet completely invisible. (Screenupdating = false) does not work for some reason..

 

[hiker95]

Trying to Learn VBA (Excel)

Drumin_Phreak,

See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 05/21/2017

See Reply #2 at the next link:

https://www.mrexcel.com/forum/excel-questions/1008151-preparing-job-interview-excel-test.html