Hi guys,
I have two doubts related to some VBA code and would be very grateful if you could help me.
1. I have a VBA macro with some variables defined and in that code I'm trying to use standard Excel formulas. However, I'm not sure how to use Excel formulas + the code variable(s) together. More specifically, here is a snippet of my code -
' some code here along with start of the IF statement
ElseIf k = 5 Then
Worksheets(i).Cells(10, 10).Formula = "=IF(ISERROR(Worksheets(i).Cells(4, 10).Value * Worksheets(i).Cells(5, 10).Value), 0, Worksheets(i).Cells(4, 10).Value * Worksheets(i).Cells(5, 10).Value)"
Worksheets(i).Cells(11, 10).Formula = "=IF(ISERROR(Worksheets(i).Cells(6, 10).Value * Worksheets(i).Cells(7, 10).Value), 0, Worksheets(i).Cells(6, 10).Value * Worksheets(i).Cells(7, 10).Value)"
Worksheets(i).Cells(12, 10).Formula = "=IF(ISERROR(Worksheets(i).Cells(8, 10).Value * Worksheets(i).Cells(9, 10).Value), 0, Worksheets(i).Cells(8, 10).Value * Worksheets(i).Cells(9, 10).Value)"
Worksheets(i).Cells(j, k).Value = Worksheets(i).Cells(12, 10).Value + Worksheets(i).Cells(11, 10) + Worksheets(i).Cells(10, 10)
' some code here along with end of the IF statement
As you can see, I have a variable "i" which loops through multiple sheets of the workbook doing some calculations in cells (10,10), (11,10), (12,10) and (j,k) respectively ("j", "k" too are variables). When I run my macro, it runs totally fine if these 4 lines are commented. But otherwise I get an error in these lines. So could someone tell me *specifically* what is the correct syntax for this statement, and also what one must do *in general* if one wants to use Excel formulas in VBA?
2. I also have one other question. How do I check if a particular value exists in a range (in my case the value is "#N/A Requesting Data...")? In my code currently I'm looping through every cell in that range and checking if
#N/A Requesting Data..." <> Worksheets(i).Cells(j, k)
and then doing some particular task. Although this is working, it is obviously very expensive in terms of time. Is there a better way to check if a value exists in a range which you define?
Hope someone can help me at the earliest.
Thanks a lot,
exceluser_1
Bookmarks