Excel Macro VBA Tip 10 - Enter Array Formulas into Cells in Excel with a Macro

Excel is my source document and Word is my template. I have put together a macro in Excel which opens my Word template and populates my bookmarks with the applicable Excel cells. My Excel document is mostly percentages which I am transferring to Word. While I was drafting my source document I was hard coding my percentages in temporarily (e.g. 30.00%, 40.00%, etc) to get it working. Well my "draft" Excel document works perfectly, but when I moved my "draft" Excel document into a "live" working document I replaced my temporary hard coded percentages into formulas which give me my percentages. Of course my macro doesn't work now.

My assumption is the code I put together in my "draft" is not populating my Word bookmarks due to the formulas I added to my Excel cells. I'm also guessing this is not a new problem and hopefully there is an easy fix that I am just not thinking of. I have pasted my code below. Any suggestions would be greatly appreciated.

Sub createTemplate()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim Wal_Cat_B As Excel.Range
Dim Wal_Cat_D As Excel.Range

Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With
Set myDoc = wdApp.Documents.Add(Template:="C:\Test.doc")
Set Wal_Cat_B = Sheets("Sheet1").Range("B2")
Set Wal_Cat_D = Sheets("Sheet1").Range("B3")

With myDoc.Bookmarks
.Item("Wal_Cat_B").Range.InsertAfter Wal_Cat_B.Text
.Item("Wal_Cat_D").Range.InsertAfter Wal_Cat_D.Text

End With

errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
End Sub

I would like to use a macro to move to different cells , entering a value
into the cell before moving to the next one, can this be done?

I want to use an appliction to start my macro when the "enter" key is pressed. I typed in the code below, but it does not work. Any clues?
~~~~~(lockcells is the name of the macro I want to run)~~~~~~~


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.OnKey "~", "lockcells"

End Sub

HI Everyone,


I have few things in my mind and i want a macro to deliver that.

problem statement:

i am using a share excel workbook wherein all of us work in single sheet which is used for updating the information.We have kept the 1st column as "Analyst" column and in that, the names of the people who are going to feed the data. so the concerned person selects his name in dropdown of analyst column and starts feeding or inputting the data in the columns.

Now i have inserted a formula in 3 different cells in excel which would read and give me the result "true or false". that is the data what you have entered is going to be matched with the data required to be entered, if it mismatches. we get the resut as "false"

Now if suppose my formula shows "False", that implies i have done incorrectly, now i need a macro which would read that and if all the 3 cells show "false", then the macro should not allow me to feed the data in next line. Once i correct it and it shows true then i am allowed to enter data in next row.


This will help in correcting my mistake the next second and i do not have to wait for my accounts to be audited. As i can myself audit and correct the mistake before someone audits my accounts.


Awaiting Reply

Thanks

I have a couple of array formulas that were expertly suggested on this very forum. They function spectacularly, but now I need to incorporate them into my code.

The catch is that I am not sure yet if I will a WorksheetFunction solution, or more the likes of the actual "live" formula, e.g., "=SUM(A:Z)"

In either case, I am not sure what the equivalent of CSE is in VBA, or whether the braces/curly brackets can be manually inserted in the live formula.

Here's my new signatu "Help me MVP, you're my only hope!"

Im running a macro in excel that i didn't created but trying to figure out why is giving an error. The error that i get says "Type Mismatch" and that's it, but when i try to debug the macro it goes to this line

Code:

 
Dim iRow As Integer
iRow = CInt(Replace(sLastRange, "$K$", ""))

and i get the message. The macro is basically pulling data from store procs and with some formulas in the macro it display and format some fields in excel. The rest of the formulas are based on iRow but since it's failing here, then I don't get anything else. Any help will be
appreciated. I'm posting the line of code where is failing, I don't know much about macros, so I don't know if this is a correct format either, let me know if I have to put more code there.
Thanks in advanced.

Hi,
I want to these excel formula in macro does anyone have any idea?

"=IF(A$1=Sheet2!A:A,VLOOKUP(Sheet1!A$1,Sheet2!A:B,2,0),
IF(A$2=Sheet2!A:A,VLOOKUP(Sheet1!A$2,Sheet2!A:B,2,0),
IF(A$3=Sheet2!A:A,VLOOKUP(Sheet1!A$3,Sheet2!A:B,2,0),
IF(A$4=Sheet2!A:A,VLOOKUP(Sheet1!A$4,Sheet2!A:B,2,0),
IF(A$5=Sheet2!A:A,VLOOKUP(Sheet1!A$5,Sheet2!A:B,2,0))))))"

Also if i want A$'n" then how can i use it in Formulas as well as in Macro.

Hello, I have a problem.

The following code line fails:

Worksheets(1).Range("AA7") = "=IF(A5=0;0;1)"

The following code does not fail:

Worksheets(1).Range("AA7") = "IF(A5=0;0;1)"

so i guess the formula difference is why it does not work.

I get error 1004, and when i try to Evaluate("?IF(A5=0;0;1)") it returns
Error 2015


Why does my code fail?

Thanks!

-Anders

Hi all -

I'm a rookie with macros and could really use some help.

This workbook is constantly exported from our accounting system. I currently have a macro under personal that the accountant can run to delete the unnecessary columns, but I would like to add another automation -

I need to divide each cell in column G by the cell in column E in that same row, then put the answer in column H of that same row. The first entry will always start in row 5, but the last entry is always unknown.

Any help would be great! Thanks!

Hi , I am trying to add something to a macro, to make it calculate the formulas on sheet (Fund1) when new data gets imported there.

look at row 658, The latest price was copied there, but columns C to F are empty, I need those formulas to be recalculated for that row as well.
All columns have dynamic range names, is there a way to put that in a macro and make it calculate formulas?





would appreciate any help.
thanks