UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA Variable With Formula, Office 2007    
 
   
davidgk
post Nov 5 2018, 07:06 PM
Post#1



Posts: 11
Joined: 27-May 15



hi
im trying to multiply column 1 by column 2 thru VBA , but i want to keep the the fornula in the cells,
but when im running the folowing code i get a #NAME error instead of the formula


Sub automation_loop()

Row = 2
Do While Cells(Row, 1) <> “”

Cells(Row, 4).FormulaR1C1 = "=Cells(Row, 2) * Cells(Row, 3)"

Row = Row + 1
Loop
End Sub



when i do the same thing thing just to show the results it works fine

Sub automation_loop()
Row = 2
Do While Cells(Row, 1) <> “”
Cells(Row, 4).Value = Cells(Row, 2) * Cells(Row, 3)

Row = Row + 1
Loop

End Sub
But i do want to keep the formula itself in those cells just in case i change 1 qty it should automatically
update the result

please help
thanks



Go to the top of the page
 
WildBird
post Nov 5 2018, 09:48 PM
Post#2


UtterAccess VIP
Posts: 3,466
Joined: 19-August 03
From: Auckland, Little Australia


CODE
=IF(LEN(A2)>0,A2*B2,"")


OR define it as a table, and simply put in A2 * B2 - that way it will be dynamic depending on how many rows there are.




--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
Debaser
post Nov 6 2018, 07:40 AM
Post#3



Posts: 46
Joined: 11-October 18



No need to actually loop generally for something like that:

CODE
Sub automation_loop()
    Range("D2:D" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = "=RC2*RC3"
End Sub
Go to the top of the page
 
davidgk
post Nov 6 2018, 12:11 PM
Post#4



Posts: 11
Joined: 27-May 15



Hi
The reason i need a loop , is that i want with 1 click to do a bunch of rows like thousands sometimes ( it can vary )
Thanks
Go to the top of the page
 
Debaser
post Nov 6 2018, 12:27 PM
Post#5



Posts: 46
Joined: 11-October 18



You still don’t need to loop as it’s the same formula in every row. Try it. smile.gif
Go to the top of the page
 
davidgk
post Nov 6 2018, 01:30 PM
Post#6



Posts: 11
Joined: 27-May 15



It works great
Thanks a million
Go to the top of the page
 
Debaser
post Nov 7 2018, 08:00 AM
Post#7



Posts: 46
Joined: 11-October 18



You're welcome.
Go to the top of the page
 
JonSmith
post Nov 7 2018, 08:17 AM
Post#8



Posts: 3,957
Joined: 19-October 10



QUOTE
OR define it as a table, and simply put in A2 * B2 - that way it will be dynamic depending on how many rows there are.


This is a better suggestion, use tables and structured references. Unnecessary VBA code like this or 'empty' rows with placeholder formula's are bad. You don't need any VBA code here so strip it out. The table and structured reference will handle everything you need.
Go to the top of the page
 
Debaser
post Nov 7 2018, 08:47 AM
Post#9



Posts: 46
Joined: 11-October 18



Surely that assumes quite a lot about the circumstances?
Go to the top of the page
 
JonSmith
post Nov 7 2018, 09:34 AM
Post#10



Posts: 3,957
Joined: 19-October 10



Not really, its based on a lot of experience. There are pretty much zero scenarios where populating a column with formula's is better achieved with VBA over using a structured reference. I can probably think of way more issues from using VBA vs not using VBA. Array formula's would be a quick example. They are a nightmare in VBA.

If you can think of a downside to the table and structured references approach then please share it?
Go to the top of the page
 
Debaser
post Nov 7 2018, 06:00 PM
Post#11



Posts: 46
Joined: 11-October 18



You’re still assuming your experiences relate to these (as yet unspecified) circumstances. If this is not a pre-existing workbook for instance, you’d still need VBA to create the table, which could be total overkill. Table performance, especially with larger datasets, is not great - although the latest 2016 builds have overcome some of that. I’m not saying it’s not the right option, but to state that it is categorically a better option is, in my opinion, a bit much.

Structured references do have their drawbacks too - creating absolute references is ugly at best.
This post has been edited by Debaser: Nov 7 2018, 06:02 PM
Go to the top of the page
 
davidgk
post Nov 7 2018, 11:27 PM
Post#12



Posts: 11
Joined: 27-May 15



This will be mostly used on imported data, and will be used in a VBA with a bunch of other stuff , like formatting , combining data, etc. etc.
So for me its better I think to have it in code and do everything with 1 click


(Although I have far less knowledge than you guys)
Thank again for all your help
Go to the top of the page
 
JonSmith
post Nov 8 2018, 03:33 AM
Post#13



Posts: 3,957
Joined: 19-October 10



Sorry Debaser but I totally disagree.
Whatever limitations have existed around some performance decrease is massively outweighed by all the advantages. The formula's are way easier to read, you don't need to copy down formula's, pivots based on the table grow automatically, if you want to use VBA with it then its much more robust to work with tables.
You assertion that making the table is somehow 'overkill' is nonsense. Making tables in VBA is super easy and once you have a table its so much easier to add data to that table in addition to being super robust compared to the 'find the last used cell and offset by 1' method which is the normal alternative.

Even now we have more information a table could help more. There is a data import, great, tables are designed for that and can be linked. Formatting needs to be done, great, table columns also have 'default' formatting meaning you can set the formatting once and it'll apply that to all the rows.

Its really simple, if you have a table of data, use tables and structured references.

If we take your example, if the used range is messed up. at some point someone entered something in row 54821 but only cleared it and didn't delete it then the code you posted could copy down a formula many more thousand rows than required. There might even be a second set of information below this data that messed up the rows.count.

As far as assumptions go, the method I propose is far more robust at dealing with all of this random circumstances that could occur whereas yours will fall down given a wide number of variables.
So incase it wasn't clear before, yes it is based on experience, experience that given unknowns a table always performs better and more consistently than counting rows and adding formula's with VBA code.
Go to the top of the page
 
Debaser
post Nov 8 2018, 03:58 AM
Post#14



Posts: 46
Joined: 11-October 18



No need to be sorry, you’re entitled to your opinion. I’m just saying that’s all it is: your opinion. I appreciate it’s based on your experience just as you will presumably appreciate that my opinion is based on my experience.

In my opinion, tables are not always the best solution, and they are far from perfect. I have datasets that grind excel to a halt if I use a table but work fine without. Structured referencing is also a matter of taste: I know people who swear by it, but as many who swear at it. In Excel, as in most things, there is no one thing that fits every scenario. That is the point I’m making. You are free to disagree.

As an aside, my code did not mention usedrange.
Go to the top of the page
 
JonSmith
post Nov 8 2018, 04:31 AM
Post#15



Posts: 3,957
Joined: 19-October 10



I never said it wasn't my opinion. I was disagreeing with you about assuming alot about the circumstances. As I've described, the table method is much more robust in pretty much most circumstances ergo it will most likely work better. Its more of an assumption of all the circumstances that your code works, I listed a number of scenarios that could break it so whilst your method assumes all of those aren't occurring the method I support assumes any of them could be occuring, and the method would still work.

Fair point on the UsedRange, because I find the rows.count method so sloppy I didn't realise it does infact ignore blank cells unlike usedrange. That in itself can of course sometimes be an issue. The code only works if there is also a value in the A column, in a dataset where column A is optional then a number of trailing rows can easily be missed if they don't have a value in column A.
Go to the top of the page
 
Debaser
post Nov 8 2018, 04:50 AM
Post#16



Posts: 46
Joined: 11-October 18



I will agree that my code would probably be better looking at column B since it contains one of the multiplicands.

For the rest, I'll agree to disagree. wink.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th November 2018 - 09:26 PM