Excel Macro - If Cell contains a certain word then run the formula

[Kovela]

NOTE:I'm new to this forum & to coding in Excel & English isn't my first language so excuse me if I'm bad at explaining things I will do my best.


Hello, there my name is Kovela. I am a financial analyst for a new company. I need to figure out a code that would help me in summary "If Cell contains "TEXT" then the formula will run and give me results"


Let me explain what I do briefly,

1- I will gather data from online paste it in Sheet A. --> The data I gathered has endless users, their expenditures in dollar amount, and multiple totals between the rows.
2- I will then switch to Sheet B that has the formulas ready to view as a summary of the totals in Sheet A calculated in different formulas to analyze & help me make decisions
3- With that being said, posting the data to Sheet A will make the "Total" shifts in row numbers. That means I have to manually update the formula from the old row # to the new row #



What I would like,

1. It would be nice if there is a code that will help to find those certain phrases in row number and column number in order to get the exact cell that has a dollar amount and calculate it in the other sheet

I was thinking perhaps a "what if" would help but I have zero skills in coding but would love to be able to start this as a first successful project. Thanks in advance

 

[Yongle]

The code below asks user to enter search text into input box
- all cells in SheetA are searched
- results (found cell address(es) & text) are dumped to message box

If you need help in adapting this let me know

Paste code in standard module

Sub SearchBox()
Const sName = "[COLOR=#ff0000]SheetA[/COLOR]"
Dim SearchFor As String, cel As Range, msg As String, FirstFound As String

    SearchFor = Application.InputBox("Enter text", "Search Text")
    
    On Error Resume Next
    With Sheets(sName).Cells
        Set cel = .Find(SearchFor, LookIn:=xlValues)
        If Not cel Is Nothing Then
            FirstFound = cel.Address
            Do
                msg = msg & vbCr & cel.Address(0, 0) & vbCr & cel
                Set cel = .FindNext(cel)
            Loop While Not cel Is Nothing And cel.Address <> FirstFound
        End If
    End With
    If msg = "" Then msg = "No hits"
    MsgBox msg
End Sub

 

[Kovela]

Thanks for your response.

What should I put in under "Enter Text" and "Search Text"? I'm thinking search text = Total ?

SearchFor = Application.InputBox("Enter text", "Search Text")

 

[Yongle]

Please provide one complete example of what you want...

Example of "TEXT" to search
Is VBA searching the cell value or the formula?
Which cells are to be searched?
Which sheet?
Calculate what in the other sheet? (Which formula?)
What is the trigger to make VBA run? (Cell change? Button?)

thanks

 

[Kovela]

Sheet A will have in a row that has "Total Revenue" this row will be changing based on the number of expenses calculated that month. we can have 36 expenses or 50 expenses. so based on the number of expenses the rows of total revenue will change.
Once it locates "Total Revenue" Row number, I would like it to find column H (which is mostly fixed column letter/number), to find that total revenue cell. After it locates that, I would like it to take that number in the row (say for example 36H) to put that number in a formula in sheet B

is this even possible thing to code?


thanks

 

[Yongle]

Do you need VBA for this? There are several formulas that can be used. Try this

In SheetA
- in cell A36 enter text "Total Revenue"
- in cell H36 enter number 123

In SheetB
- in cell A1 this formula
=VLOOKUP("Total Revenue",SheetA!A:H,8,0)
(formula returns 123)

- in cell A2 this formula
=MATCH("Total Revenue",SheetA!A:A,0)
(formula returns 36 - the row number of the match)

- in cell A3 this formula
=INDEX(SheetA!H:H,MATCH("Total Revenue",SheetA!A:A,0))
(formula returns 123)

 

[Kovela]

Thank you for your response. The codes were perfect for the first couple cells. the other part of the process is similar to whatever I explained earlier however there is an added formula to it. So locate Total Revenue from Sheet A and Operating Income from Sheet C and then use Sum up formula to calculate.

here is what I have in these cells originally:
= -'Sheet A'!AKN205+'Sheet A'!H205+'Sheet C'!H155

AKN205 = Operating Income (in sheet A)
H155 = Operating Income (Sheet C)


As you can see, the formula has to be updated monthly to where the operating income total come in (which row) I was hoping like how you did earlier, find a formula to find the operating income in sheet A and sum up with the operating income found in Sheet C. Is this possible?


thanks !

 

[Kovela]

I did break down the formulas based on your previous response to me. I used

= -'Sheet A'!AKN205+'Sheet A'!H205+'Sheet C'!H155


=-VLOOKUP("Operating Income",'Sheet A'!A:AKH,970,0)

=VLOOKUP("Operating Income",'Sheet A'!A:B,2,0)

=VLOOKUP("Operating Income",'Sheet C'!A:B,2,0)

Im thinking to just combine them with a plus sign and the formula should be good however, if theres a better way to do it that would help