We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How do I include a calculation in a vba if statement

Medium Priority
290 Views
Last Modified: 2012-05-11
Hi Experts,

I have added a button to my excel worksheet that copies rows from another excel worksheet within the same workbook based off of the values in a column.  What I also need to add to the code is a formula that will look at the value in the corresponding row and divide itself by every cell in that row where the cell value is not null, then paste the results in the new worksheet that I created.  Please see the sample file attached.  The code that I used on the command button to copy and paste is below.
 Example.xlsx
Private Sub Button_Click()
Dim rng As Range
Dim cell As Range
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim r As Integer
r = 2
Set ws = Sheets("HighRollers")
Set ws1 = Sheets("Example")
Set rng = ws.Range("R2:R" & ws.Cells(1048576, "R").End(xlUp).row)

For Each cell In rng
If cell.Value = "Daily" Then
    cell.EntireRow.Copy Sheets("Example").Cells(r, 1)
    r = r + 1
End If
Next cell

End Sub

Open in new window

Comment
Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT

Commented:
hi,

Can you please upload the file again in a different format such as .xls, .xlsm, or .xlsb?
(.xlsx prevents any code being saved within the file)

Also, I'm having trouble understanding exactly what you want so can you please include some more explanation in the file with a "before & after" example?

Rob

__________________
Rob Brockett. Always learning & the best way to learn is to experience...

Author

Commented:
StephenJR,

Will ".PasteSpecial operation:=xlPasteSpecialOperationDivide " do the division like I need it to?  I will try that
   

Author

Commented:
StephenJR,
In the snippett below, can you explain your logic?

Sheets("Example").Cells(r, 4).Resize(, 9).SpecialCells(xlCellTypeConstants) _
                    .PasteSpecial operation:=xlPasteSpecialOperationDivide
I assumed you were wanting to divide each of the numbers you pasted by the value in column Q. Is that right? That's what it does. The special cells bit is to avoid pasting the blanks which will result in zero. Does it actually do what you want?

Author

Commented:
broro183,

I do not have any code within the sample that I provided.  I just pasted the code that I used in the original file.  What I provided was an example.  I want to copy all the rows from the "High Rollers" sheet where the value in column "R" is equal to "Daily".  Before I paste that information into the "Example" worksheet, I need to divide every numeric value within the row by the values contained in column Q respectively, then paste those results.
My code pastes the values as is and then does the division, but the result is the same.

Author

Commented:
StephenJR,

I modified my code to match the 'If' statement that you provided me and I get this error message: No cells were found.  
I only changed this part: "Sheets("Example").Cells(r, 4).Resize(, 9)." to "Sheets("Example").Cells(r, 8).Resize(, 9)." to fit my actual file
That suggests you have all blanks. I may have made a mistake. If you change to

Resize(,13)

does that work?

Can you post a sample of your actual workbook if not?

Author

Commented:
I don't have all blanks in the cells.  Unfortunately, I cannot post the actual file as there is sensitive information contained within it.  Please see an example of the actual file attached. Sample.xlsm
CERTIFIED EXPERT

Commented:
It looks like StephenJR has the solution under control. My additional thoughts are:

- Have you tried recording a macro which applies an Autofilter for "Daily" & then copies/pastes the visible cells before processing StephenJR's code?
Using an autofilter is likely to be much faster than looping cell by cell esp. as the size of the dataset increases.

- how big is your dataset?
If it is >16k rows & you use an autofilter-copy visible cells, you could be at risk of hitting the specialcells limitations in pre-2010 versions of excel.

- I suggest testing " if Not (visiblespecialcells range is nothing) then '..." before attempting to copy/divide, as this will prevent errors if no cells have been found.

- I suggest changing "Dim r as integer" to be "Dim r as Long". I believe the compiler will internally convert code to long before processing it therefore you are saving it some "work". Also, you have used
Set rng = ws.Range("R2:R" & [b]ws.Cells(1048576, "R").End(xlUp)[/b].row)

Open in new window

which means that potentially "r" could end up being larger than the upper Integer size limit of ~32k.

 
hth
Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...

Author

Commented:
broro183, can you please apply your thoughts to the most recent sample file that I provided, it will help me to understand the logic better if I can step through it.

Author

Commented:
I hope that no one has forgotten about me.  I really need to get this done tonight.
CERTIFIED EXPERT

Commented:
nope, not forgotten but I should be asleep!

StephenJR,
I'll have a go, since Sally's waiting, but let me know if you're still working on it...

Rob
CERTIFIED EXPERT

Commented:
hi,

I do not have any code within the sample that I provided.  I just pasted the code that I used in the original file.  What I provided was an example.  I want to copy all the rows from the "High Rollers" sheet where the value in column "R" is equal to "Daily".  Before I paste that information into the "Example" worksheet, I need to divide every numeric value within the row by the values contained in column Q respectively, then paste those results.

Has column R become column AO & has column Q corespondingly become column AN in the new file?

Rob

Author

Commented:
Thank you broro183 for helping me out!!  Yes, column R has become column AO and column Q has become column AN.
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT

Commented:
hi DaintySally,

Did you get the code to work?

Rob

Author

Commented:
Yes for the most part.  Thank you for asking.  However, I need to add some code in here that will not show the #DIV/0! when there are blanks and I need to figure out how to get the code to insert rows and not overwrite another header row that sits on row 50 of the worksheet.  Any ideas?
CERTIFIED EXPERT

Commented:
Can you please post the latest complete version of the code you are working with?

Rob
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.