Hi Folks,

I have recently inherited care of an Excel XP model that contains the

following calculations, containing the # symbol.

Amount = Amount / 100#

NewValue = OldValue * -1#

I can see where the variables Amount, NewValue and Old Value are declared

but nothing on the #.

The end results sor t of make sense if the # symbols were in fact the %

sign. Other than that I am totally confused.

Does this use of the # symbol make sense to anyone.

TIA

John Howard

Sydney, Australia

I have recently inherited care of an Excel XP model that contains the

following calculations, containing the # symbol.

Amount = Amount / 100#

NewValue = OldValue * -1#

I can see where the variables Amount, NewValue and Old Value are declared

but nothing on the #.

The end results sor t of make sense if the # symbols were in fact the %

sign. Other than that I am totally confused.

Does this use of the # symbol make sense to anyone.

TIA

John Howard

Sydney, Australia

- VBA: Excel functions in VBA
- Opening then later Closing an Excel file in VBA>
- Using Excel Formula in VBA
- Use of Excel Formulas in VBA (in loops)
- Is there anyway to create a clone of an excel sheet in vba?
- Help Please ! Calculations outside of excel and in VBA
- Using Excel formulas with variables in VBA
- Excel formula in vba
- Clearing an excel textbox in vba
- How to Use Excel Functions in VBA
- Array formula in VBA: Calculating the variance of the product of two ranges
- Change colour in Excel (chart) object in VBA
- Embed Excel Formula in VBA
- Excel function in VBA
- How to use Excel Functions in VBA?
- Using excel functions in vba
- Trouble with excel references in VBA...
- Incorporating Excel formula in VBA
- How do you use the Excel EOMONTH function in VBA?
- Excel function vs VBA function dictionnary
- Excel formulas in vba for beginner
- Lookup Date In VBA
- Naming an Excel sheet in vba
- Using Worksheet Function in VBA?

How I define the excel function in VBA if I want to use SUM -function which should sum up values in cells A1 and C2 for example. I have readed VBA help but procedure is still unclear.

In VBA I need to be able to Open and when finished Close an Excel File.

There are several files in my folder. Folder is named Thom Drive.

All the file names start with 01, 02, and 03 Etc... They reference the

weeks in a year.

The 1st two (2) positions in the file name are unique.

How would I go about this using VBA?

..

Thanks in advanced.

--

Richard

Can we use the excel formula in VBA directly without putting them on to worksheet.

FOr example a formula to sum a range like =SUM(A1:A10). As of now I am putting this to a dummy cell through VBA code, then getting value back again from that.

Now the question is : Can we use this SUM(A1:A10) formula directly in the code and get the value without pasting this formula to excel worksheet and get the summed value from that cell. Is there any method to achieve this.

Please help. Thanks a lot in advance for the expert advice.

Regards

Anand

I am new in VBA.

I want to use excel functions in VBA Loop.

fyr,

Formulas can be use by following way without Loop.

Dim fs As Worksheet Set fs = Worksheets("sheet1"); Dim os As Worksheet Set os = Worksheets("sheet2"); With fs .Cells(4, 2).Formula = "=int(sheet2!e4*sheet2!b13)" End With But now I want to use this Blue color lines in some range. i.e For i=4 To 10 With fs .Cells(i, 2).Formula = "=int(os.Cells(i,2)*os.Cells(i+1,2))" End With Next iBut Its seem to be wrong and its not working.

Can anyone help me to find the correct way to do it?

Much appreciated.

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 statementAs 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

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

I want to use following formulae in vba and I want excel to update this every time worksheet is opened as it involves calculation of days using today function. Here are my formulae to be used as vba code.

In Column M2:M300=IF(ISTEXT(N2),"First Reminder Sent",IF(ISBLANK(L2),"",IF(L2<=TODAY()+30,"First Reminder Due","")))

In Column O2:O300=IF(ISTEXT(P2),"Second Reminder Sent",IF(ISBLANK(L2),"",IF(L2<=TODAY()+14,"Second Reminder Due","")))

In Column Q3:Q300=IF(ISTEXT(S2),"Final Reminder Sent",IF(ISBLANK(L2),"",IF(L2<=TODAY()+7,"Final Reminder Due","")))

Hope I would get some help

any ideas?

cheers guys

G

This syntax doesn't seem to be working.

Anyone know the correct way to use Excel functions (eg. IsText) in vba?

Thanks ,

Paul

I have the following VBA question. I want to calculate the variance of a range. Lets assume the code looks like the following

Code:

Dim wb as Workbook Dim wb as Worksheet Dim rng1, rng2 as Range Dim Variance As Long Set wb = ThisWorkbook Set ws = wb.Worksheets("Sheet1") Set rng1 = ws.Range("A1:A1000") Set rng2 = ws.Range("B1:B1000") Variance = Worksheetfunction.Var(rng1 *rng2) '

we've changed the color scheme so that when we apply the old styles, text comes up as lime green. i was wondering if there is a way in vba to edit the color application. tell it that the text color is custom color no. 4 instead of 5?

they are MS Excel objects in VBA in a sheet. when i look in the "Chart Type" under Custom, the charts are "Built In" [normally one might expect to find them under "user-defined"].

anyway,this guy was super smart and if i have to start from scratch, it will be a much bigger project.

Ideas?

excelvbascreenshot1.jpg

TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7) where StartDate is the date which you are trying to find the corresponding week number (for example Now().

Here's the code:

Cell.Offset(0, 15).Formula = "=mduration(" & Chr(34) & Date & Chr(34) & ", " & Chr(34) & Cell.Offset(0, 6).Value & Chr(34) & ", " & Chr(34) & Cell.Offset(0, 4).Value / 100 & Chr(34) & ", " & Chr(34) & Cell.Offset(0, 14).Value / 100 & Chr(34) & ", " & Chr(34) & Cell.Offset(0, 5).Value & Chr(34) & ")"Cell.offset(0,14).value refers to yield. It has the following code which works fine:

Cell.Offset(0, 14).Formula = "=yield(" & Chr(34) & Date & Chr(34) & ", " & Chr(34) & Cell.Offset(0, 6).Value & Chr(34) & ", " & Chr(34) & Cell.Offset(0, 4).Value / 100 & Chr(34) & ", " & Chr(34) & Cell.Offset(0, 12).Value & Chr(34) & "," & Chr(34) & 100 & Chr(34) & "," & Chr(34) & Cell.Offset(0, 5).Value & Chr(34) & ")*100" Cell.Offset(0, 14).Value = Cell.Offset(0, 14).ValueAny suggestions as to how to combat the type mismatch error?

While using Excel Search Function in VBA programming, I got

"Sub or Function not defined!!" Error...

But need to use the Search funtion..

So please tell me how to use it?

Thanks in advance

john best

Anyone know if we can use the excel built in function, e.g. sum(), countif(), offset() , in vba? if yes, what is the syntax like?

thanks a lot

I am trying to write some code that will help automate Excel reports. I am running into trouble, when I try to incorporate Excel formula's in VBA.

Here's some code that I am working with - but I am getting run time errors.

Regards - Exxel..

Sub InsertFormula() 'Variable Declaration.. Dim Arg1, Arg2 As String Dim ProdRowNum As Integer Dim ProdStartPeriod As Integer Dim ProdEndPeriod As Integer Dim TotalUnits As Integer 'RUN TIME ERROR OCCURS HERE. THESE MATCH STATEMENTS WERE WORKING OK Y'DAY - BUT TODAY 'I AM IN TROUBLE - ANY ADVICE? ProdRowNum = "=MATCH(C6,INDIRECT(ADDRESS(1,3,,,$C$8)):INDIRECT(ADDRESS(100,3,,,$C$8)),0)" Worksheets("Summary").Range("$B24").Value = ProdRowNum ProdStartPeriod = "=MATCH($C$9,INDIRECT(ADDRESS(1,1,,,$C$8)):INDIRECT(ADDRESS(1,100,,,$C$8)),0)" Worksheets("Summary").Range("$B25").Value = ProdStartPeriod ProdEndPeriod = "=MATCH($C$10,INDIRECT(ADDRESS(1,1,,,$C$8)):INDIRECT(ADDRESS(1,100,,,$C$8)),0)" Worksheets("Summary").Range("$B26").Value = ProdEndPeriod 'Arg1 = ProdRowNum.Value & "," & ProdStartPeriod.Value & ",,,$C$8" 'Worksheets("Summary").Range("$B27").Value = Arg1 'Arg2 = ProdRowNum.Value & "," & ProdEndPeriod.Value & ",,,$C$8" TotalUnits = "=SUM(" & ProdRowNum & "," & ProdStartPeriod & ")" ' ALSO NEED HELP IN INCORPORATING VALUES OF LOCAL VARIABLES INTO EXCEL FORMULA - AM GETTING RUN TIME ERRORS HERE ALSO. TotalUnits = _ "=SUM(INDIRECT(ADDRESS(" & ProdRowNum & "," & ProdStartPeriod & ",,,$C$8)):INDIRECT(ADDRESS(" & ProdRowNum & "," & ProdEndPeriod & ",,,$C$8)))" Worksheets("Summary").Range("$B27").Value = TotalUnits End Sub

I am a beginner in VBA programming. I have programmed before in PERL, C etc, but suffice it to say that it's been a while. I am trying to write some code that will help automate Excel reports that I get on a daily basis for which I need your help/advice. I am running into trouble, when I try to incorporate Excel formula's in VBA. I am also facing issues in incorporating the values of local variables into Excel formula.

Here's some code that I am experimenting with - but I am getting run time errors.

Sub InsertFormula() 'Variable Declaration.. Dim n As Long Dim Addr As String Dim test, Arg1, Arg2 As String Dim ProdRowNum As Integer Dim ProdStartPeriod As Integer Dim ProdEndPeriod As Integer Dim TotalUnits As Integer For n = 23 To 28 Addr = "$" & "A" & n Worksheets("Summary").Range(Addr).Formula = "=RAND()" Next n test = """cfg""" & ",C6" Worksheets("Summary").Range("$B23").Formula = _ "=CONCATENATE(" & test & ")" 'RUN TIME ERROR OCCURS HERE. THESE MATCH STATEMENTS WERE WORKING OK Y'DAY - BUT TODAY 'I AM IN TROUBLE - ANY ADVICE? ProdRowNum = "=MATCH(C6,INDIRECT(ADDRESS(1,3,,,$C$8)):INDIRECT(ADDRESS(100,3,,,$C$8)),0)" Worksheets("Summary").Range("$B24").Value = ProdRowNum ProdStartPeriod = "=MATCH($C$9,INDIRECT(ADDRESS(1,1,,,$C$8)):INDIRECT(ADDRESS(1,100,,,$C$8)),0)" Worksheets("Summary").Range("$B25").Value = ProdStartPeriod ProdEndPeriod = "=MATCH($C$10,INDIRECT(ADDRESS(1,1,,,$C$8)):INDIRECT(ADDRESS(1,100,,,$C$8)),0)" Worksheets("Summary").Range("$B26").Value = ProdEndPeriod 'Arg1 = ProdRowNum.Value & "," & ProdStartPeriod.Value & ",,,$C$8" 'Worksheets("Summary").Range("$B27").Value = Arg1 'Arg2 = ProdRowNum.Value & "," & ProdEndPeriod.Value & ",,,$C$8" TotalUnits = "=SUM(" & ProdRowNum & "," & ProdStartPeriod & ")" ' ALSO NEED HELP IN INCORPORATING VALUES OF LOCAL VARIABLES INTO EXCEL FORMULA ' AM GETTING RUN TIME ERRORS IN THE STATEMENT BELOW TOO... - ANY ADVICE ? 'TotalUnits = _ "=SUM(INDIRECT(ADDRESS(" & ProdRowNum & "," & ProdStartPeriod & ",,,$C$8)):INDIRECT(ADDRESS(" & ProdRowNum & "," & ProdEndPeriod & ",,,$C$8)))" Worksheets("Summary").Range("$B27").Value = TotalUnits End SubThanks in advance for all your help.

Regards,

-Exxel...

Application.WorksheetFunction.Sum

Application.WorksheetFunction.Average

etc

I cannot seem to get this to work when using the EMOMONTH function. Is this because it is an addin and must be referenced differently?

Thnx

Is there some kind of translation dictionnary between excel functions and vba function?

For example :

find in excel = instr in vba

lower in excel = Lcase in vba

etc...

Thank you for your help

Please, please help!

I need to add to existing vba macro next functionality:

add 3 collumns (N,O,P) to existing ****

Set formulas to new collums as following

(I'm copiing from one cell of each collum)

N104 should have following value

=IF( LEN($K104) > 0,DATEVALUE(MID($K104,1,FIND("GMT",$K104)-1)) - DATEVALUE(MID($L104,1,FIND("GMT",$L104)-1)),NOW()-DATEVALUE(MID($L104,1,FIND("GMT",$L104)-1)))

O233

=WEEKNUM(DATEVALUE(MID($L233,1,FIND("GMT",$L233)-1)))

P562

=WEEKNUM(IF( LEN($K562) > 0,DATEVALUE(MID($K562,1,FIND("GMT",$K562)-1)),0))

Thanks in advance!

I have a thought that I could try to convert the date to an integer, and do lookup on the integer, but even if it works it seems like an escape from doing things the right way. I can get worksheetfunction.vlookup to work when I am not using a date as the lookup field.

Sorry I am not enclosing the code. I have changed it so much trying to find a workable solution that it is not much use now.

there is some thought that worksheetfunction.vlookup and worksheetfunction.match don't work in VBA when the lookup field is a date. Possibly this is just an Excel glitch, but if there is a way to fix, that would really make things easier. thanks in advance.