VBA Formula

Being somewhat new to VBA for Excel, I am trying to get this formula to auto copy down my column:

=IF(H55="2100A","KM06AG",IF(H55="3007A","KM06AG",IF(H55="3007C","KM06AG",IF(H55="2100D3","SR07H3",IF(H55="1107","SR17R6",IF(H55="3007H","KM06AG",""))))))

I have searched all sorts of information and get it close but can't figure out the syntax issue I continue to have in my formula.  It works perfect in Excel.  But when I put it into my VBA code I get errors.  I tried changing " to ' and no luck around my if statement values.  Can someone tell me the best way to copy this down via code?

Basically, when a user imports a new record, I want it to look in cell I3 and copy it down column I so the user no longer has to copy or auto fill it down manually.  Tried many things but just can't get the syntax aligned properly it seems.

Thanks.
gwlanksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
the easiest solution would be to import the data into a Table and then formula will be copied automatically to new records. Select the area you are working with and hit 'insert, table' and follow the dialog. Now put any formula into the appropriate column and when you add a new row to the table that formula will be copied (as will any formatting, validation, etc).

If you are tied to a vba solution then please either post a sample workbook or explain more clearly what goes wrong and when - for example are you putting this in a worksheet change event or asking the user to press a button to execute a macro?

in principle :
range("A1") = "=a1+b1"

however you are probably having issues because your formula already contains double quotes. In such cases I build a string variable to represent the formula like this:

dim myFormula as string
dim quote as string
quote = chr(34)

myFormula = "= if(a1=" & quote & "hello World" & quote & ",1,0)"

range("a1).formula = myFormula

Open in new window


the Chr provides the double quotes so prevents the confusing syntax error.

you can also 'escape' the quotes but the escape for double quotes is double double quote: """" which makes debugging extremely difficult!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Assuming that you want to place this formula in A55, then try this.......
Range("A55").Formula = "=IF(H55=""2100A"",""KM06AG"",IF(H55=""3007A"",""KM06AG"",IF(H55=""3007C"",""KM06AG"",IF(H55=""2100D3"",""SR07H3"",IF(H55=""1107"",""SR17R6"",IF(H55=""3007H"",""KM06AG"",""""))))))"

Open in new window


Or if you want to place this formula in a range in col. A where LR is the last row with data and starting row for the formula is 55, then change the first part of the code like below. The formula part will remain the same.

Range("A55:A" & LR).Formula ="Your formula here"

Open in new window

0
gwlanksAuthor Commented:
sktneer I have the formula working except the last row portion.  I now have it copying all the way down the column except the row it is pasting and it omits that cell.  Here is what I have code wise:

LastRow = Sheets(1).Cells(Sheets(1).Rows.Count, "B").End(xlUp).Row
    
    
    Range("I3:I" & LastRow).Formula = "=IF(H3=""2100A"",""KM06AG"",IF(H3=""3007A"",""KM06AG"",IF(H3=""3007C"",""KM06AG"",IF(H3=""2100D3"",""SR07H3"",IF(H3=""1107"",""SR17R6"",IF(H3=""3007H"",""KM06AG"",""""))))))"

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I didn't get your question.

You are trying to find the last row with data in column B and as per your formula the first formula cell in column I will be I3 and will go down to the last row with data found in column B.

Where is the problem?

Can you upload a sample workbook?
0
gwlanksAuthor Commented:
The problem is if the last row of data is I53.  I import my new row of data to I54.  It copies the formula down to I53 but won't copy it to the new I54 row.  So my issue is trying to get it to also copy not only the last row but to the new current row too.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
In that case the first part of the code would be like this....

Range("I3:I" & LastRow + 1).Formula =

Open in new window

Does this resolve your issue?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gwlanksAuthor Commented:
sktneer - thank you I tried that and it worked fine I think I had hit a brain freeze yesterday after a long day and just totally skipped right over the top of that.  I have like 5 formulas and it works great now except 1 formula.  This formula will only go to the last row and won't do the +1 row and I don' t know what is causing this issue.

 Range("I3:I" & LastRow + 1).Formula = "=IF(H3=""2100A"",""KM06AG"",IF(H3=""3007A"",""KM06AG"",IF(H3=""3007C"",""KM06AG"",IF(H3=""2100D3"",""SR07H3"",IF(H3=""1107"",""SR17R6"",IF(H3=""3007H"",""KM06AG"",""""))))))"

Open in new window


It copies this formula like I said to the Last Row.  But the Last Row + 1 it will skip every single time and I can't figure out what is causing that issue to take place.  Any suggestions?
0
gwlanksAuthor Commented:
Hang tight on a comment I think I have a idea what is taking place.  Let me do some troubleshooting and I will post a reply and close out the question I think.
0
gwlanksAuthor Commented:
I fixed that problem.  I was running the formula before the data posted in my code.  It needed the value from the same row before it could evaluate the formula.  Thus, in return wasn't able to have any information to evaluate to give the information from the IF statement.  

Thank you for all your help it has been greatly appreciated.

Thanks!
0
gwlanksAuthor Commented:
As always the help has been simply amazing and I greatly appreciate it as I go through the steps of migrating from the network world to the code writing world.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome gwlanks! Glad you were able to fix the issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.