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]

Try this.
The Worksheet_Change procedure will be triggered each time a single entry is made in column C of the Report.

Put this in the Sheet module of the "Report" file :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vFile As String, fnd As Range
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Column <> 3 Then Exit Sub
If Target.Row < 6 Then Exit Sub
vFile = "Validation.xlsx"
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not bFileOpen(vFile) Then _
    Workbooks.Open ("D:\Documents\" & vFile)
Thisworkbook.Activate
Set fnd = Workbooks(vFile).Sheets(Format([E2], "@")) _
    .Columns(2).Find(What:=Target, LookAt:=xlWhole)
If fnd Is Nothing Then
    MsgBox "Value cannot be found."
    GoTo e
Else
    Target(1, 2) = fnd(1, 8)
    Target(1, 14) = fnd(1, 9)
    Target(1, 15) = fnd(1, 10)
End If
e:
Workbooks(vFile).Close saveChanges:=False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Put this in a normal module :

Function bFileOpen(wbname As String) As Boolean
On Error Resume Next
bFileOpen = Len(Workbooks(wbname).Name)
On Error GoTo 0
End Function

 

[Drumin_Phreak]

Footoo...

I cannot thank you enough! you are incredible! If you don't mind I have a question, to better understand VBA. What dictates that i needed to make a new module for the second part? is that just to keep things tidy?
Also is there anyway that i can start the search in the validation work book from the bottom up (it would find it a whole lot quicker)

Thank you again!!!

You people amaze me!

 

[Drumin_Phreak]

Footoo...


Also is there anyway that i can start the search in the validation work book from the bottom up (it would find it a whole lot quicker)

You people amaze me!

Disregard that last remark. It is super fast when the validation isn't on the network drive. So what i think i will do is every time the report is open i will save a copy of the validation work book locally.

 

[footoo]

What dictates that i needed to make a new module for the second part? is that just to keep things tidy?

By having a separate function, you can call it from any macro as needed.
I keep a number of regularly used functions in my Personal.xlsb which get called from macros whenever needed - it's easier than writing the code every time.

 

[Drumin_Phreak]

By having a separate function, you can call it from any macro as needed.
I keep a number of regularly used functions in my Personal.xlsb which get called from macros whenever needed - it's easier than writing the code every time.

What does a guy have to do to obtain a copy of that Workbook?!?!?! lol

 

[Drumin_Phreak]

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

Thank you so much! I plan on looking at this as soon as i get the time!

 

[hiker95]

Thank you so much! I plan on looking at this as soon as i get the time!

Drumin_Phreak,

You are very welcome.