If then formula

[VbaHell]

I wonder if you can help on this please

This is what i am trying to do

IF B2=C7 THEN B1=B2, IF C2=C7 THEN C1=C2, IF D2=C7 THEN D1=D2, IF E2=C7 THEN E1=E2

This follows the same pattern up to M2=C7 THEN M1=M2

Any help on this would be great please

 

[VbaHell]

Zack

This is what I am trying to achieve


This line of code looks for data in row one, when it finds data it then perform a calculation as below in Row4

LC = Cells(1, 1).End(xlToRight).Column


If LC = Cells.Columns.Count Then Exit Sub
With Cells(4, LC)
.FormulaR1C1 = "=('Total Inventory'!R[37]C+'Total Inventory'!R[42]C)/2"
'.Value = .Value
End With

I want the user the select a key word from C7
This key word will match a key word that is in row 2

B2 - M2

Example

B2 = DOG
C2 = CAT
D2 = HORSE

and so on

When there is a match I what that match to be entered in The cell above in row 1 to activate the "LC = Cells(1, 1).End(xlToRight).Column" and perform the calculation

The problem is if I have any formula in Row 1 the "LC = Cells(1, 1).End(xlToRight).Column" will pick this up and perform the calculation, what i have tried to do is use code the enter only the match in row 1 and not the formula

 

[jasonb75]

I was having a few common sense failures yesterday, as Zack noticed in my original suggestion, but in all fairness, you did ask for a formula, not a code solution.

Is there any reason for what you're trying other than to make your other code work?

If not then what you're trying to do is an unnecessary step that could be eliminated by using

Sub VbaHell()
Dim rFound As Range
Set rFound = Intersect(Rows("2:2"), ActiveSheet.UsedRange).Find(What:=Range("C7"), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
    If Not rFound Is Nothing Then
        With rFound.Offset(1)
            .FormulaR1C1 = "=('Total Inventory'!R[37]C+'Total Inventory'!R[42]C)/2"
            .Value = .Value
        End With
    End If
End Sub

 

[VbaHell]

Jason

You must be a rocket scientist, this is great

Thank you so much