Help me with Macro?

[JimmieNeu]

I have asked on another thread here if something like this was possible... it was proven possible with a Macro but it was written for me. I would like to learn how to do this for myself, but haven't found any online tutorials that would teach me the in's and out's of Macro; just a general "type this, click here, and it'll do this". That is cool and all, but it doesn't teach me what I would like to do, but it teaches what they want to do.

Can anyone here point me towards a resource to teach this to myself?
Is anyone here willing to teach?


What I am trying to accomplish is this...

A workbook that automatically creates lists based on what the workload is for the day.

Column B has a list of item names.
Column C has a list of formulas that correspond to column B.
These two columns are being used as an information bank and does not frequently change.

Column G has a list of items that is within the workload for the day.
This column is filled out manually.

What I am looking for is a macro (I think) that will take the text data in column G and look for it in column B. Then take the formula in column C and copy it to column H.


Thank you for your time!

 

[Logit]

.
Post a sample of your data (Cols B, C, G)

 

[JimmieNeu]

Column B: Left Radius Arm Combo
Column C: =A11-7.5
Column G: Blank until data is entered. If Left Radius Arm Combo is entered, I would want to copy the formula in Column C into Column H. However, Column G could have any one of the 30+ items listed in column B. And the formula in column C changes with every item in column B.

 

[Logit]

.
Ok ... hopefully I have correctly understood your goal.

The solution below does not copy the formula in the H cell, it references the value in the C2. The resultant answer is the same in the H cell. And, should you change the formula in any of the C cells
it will still display the correct answer.

The formula in H2 is presently refererncing range from row 2 to row 100. If you drag the cell down past row 100, the formula should auto edit to accommodate.



[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td][/td][td]Item[/td][td]Formula[/td][td][/td][td][/td][td][/td][td]Workday Item[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td][/td][td]Left Radius Arm Combo[/td][td]

54.8​

[/td][td][/td][td][/td][td][/td][td]Left Radius Arm Combo[/td][td]

54.8​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td][/td][td]This is the formula in H2. [/td][td][/td][td][/td][td]=IF(G2="","",VLOOKUP($G$2,$B$2:$C$100,2,0))[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td][/td][td]Select cell, then drag down the column.[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]

62.3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[JimmieNeu]

The formula in column C is what needs to be copied... not the result displayed.
I should have stated that I have tried VLOOKUP already.

 

[JimmieNeu]

This is what I used to do something very similar on another worksheet. The only difference is this is copying over more columns than I need to for this worksheet. I just do not fully understand how it works enough to be able to manipulate it to fit what I need now.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range, c As Range, cFound As Range
Dim s As String

Set Changed = Intersect(Target, Columns("J"))
If Not Changed Is Nothing Then
Application.EnableEvents = False
For Each c In Changed
s = c.Value
If Len(s) Then
Set cFound = Columns("B").Find(What:=s, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If cFound Is Nothing Then
c.Offset(, 1).Resize(, 4).ClearContents
Else
cFound.Offset(, 1).Resize(, 4).Copy Destination:=c.Offset(, 1)
End If
Else
c.Offset(, 1).Resize(, 4).ClearContents
End If
Next c
Application.EnableEvents = True
End If
End Sub

 

[Logit]

.
Help me understand.

What is the goal of copying the formula instead of only the result ?

 

[Logit]

.
I am very interested in learning the reason for copying the formula instead of the result.

However, here is a macro that will copy the formula. It is written for Sheet 1 ... so if your Sheet has a different name, you will need to edit the macro accordingly.

Option Explicit


Sub CpyFormulasCtoH()


    Dim sht As Worksheet
    Dim i As Long, j As Long


    Set sht = Sheets("Sheet1")


    Dim rngNames As Range
    Set rngNames = Range("G1", Range("G1").Offset(Rows.Count - 1).End(xlUp))
    Dim varNames As Variant
    varNames = rngNames.Value2


    Dim rngData As Range
    Set rngData = Range("B1", Range("B1").Offset(Rows.Count - 1).End(xlUp))
    Dim varData As Variant
    varData = rngData.Value2


    Application.ScreenUpdating = False


    For i = LBound(varNames) + 1 To UBound(varNames)
        For j = LBound(varData) + 1 To UBound(varData)
            If varNames(i, 1) <> "" Then
                If InStr(1, varData(j, 1), varNames(i, 1), vbTextCompare) > 0 Then
                    sht.Cells(j, 8).Value = sht.Cells(j, 3).Formula
                    Exit For
                Else
                End If
            End If
        Next j
    Next i


    Application.ScreenUpdating = True


End Sub

 

[JimmieNeu]

The reason to copy the formula is because the items come in various sizes. The formula will give a dimension to cut. Listing each possible cut for each possible item would be a list many thousands of items long... but doing so would allow a VLOOKUP to work.

 

[JimmieNeu]

This macro, even after the name change, didn't do anything... not sure where I went wrong.