Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


VBA Function Not Working

I copied this function from a VBA book and I get a Error 91 "object variable or with Block variable not set"

I have made sure that Microsoft scripting is referenced.
any clues would be appreciated.
I might add that this is a follow up on a thread I discussed with "Datasmart", thanks to John for getting me started.

Function BrowseForfolderShell() As String
Dim objshell As Object, objfolder As Object
Set obshell = CreateObject("Shell.Application")
'Uncomment next line to start at desktop
'Set objfolder = objshell.BrowseForFolder(0, "Please Select a Folder", 0, 0)
Set objfolder = objshell.BrowseForFolder(0, "Please select a Folder", 0, "c:")
If (Not objfolder Is Nothing) Then

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Working Day Function: (Day + Next 1st Working Day)
I was looking for a final result as follows

21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)

21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)

View Replies!   View Related
Trim Function Not Working
This Trim funcion is not working for me for some reason. I have attached a sample file. Can someone help me with this? You can test the macro to see. I just want to delete the trailing blank characters on Column A. Here is the code.

View Replies!   View Related
Date Function Is Not Working..
I have set of data in which i want to put filter Milestones Type <> Milestone
and a filter Actual date is greater than or equal to 30 days ago and is less than or equal to today.

View Replies!   View Related
Array Function Test Not Working
Ok, I'm sure that there is something really obvious here, but I'm just not seeing it....

Test Data starting at cell A1:

View Replies!   View Related
DMIN Function Not Working As Expected
I'm new to the DMIN function and I'm having a problem.

Cell A2: Date (rest of column is date format) (heading for the column)
Cell A2: Credit (rest of column is acctg. format) (heading for the column)
Cell A2: Debit (rest of column is acctg. format) (heading for the column)
Cell A2: Balance (rest of column is, equals balance field from previous row + credit - debit for current row, acctg. format) (heading for the column)
Column E: Notes (rest of column is generic text) (heading for the column)

Cell K1: Date (the text "Date")
Cell K2: > TODAY() (the text "> TODAY()") -- maybe this should be ="> TODAY()"?

Cell H2: =DMIN(A2:D1000,"Date",K1:K2)

I'm trying to get the minimum balance for the Balance column where the date is greater than today (lowest balance that I have in the future, which helps me see if I will be overdrafting).

I'm getting a value of 0 in H2. I have also tried =DMIN(A2:D1000,1,K1:K2) and get the same result.

View Replies!   View Related
Calculate Working Days Function
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.

View Replies!   View Related
>>> Sound User Function Not Working
I attempted to create a user function as per the link that VBA Noob posted in this post

I get #Name error. I placed the wav file Applause.wav in the same folder as the workbook. What did I do wrong?. Also, assuming I get this problem sorted out, how would I represent the condition "pass" or "fail" as in cell B3 in the Alarm formula? Would it be; Alarm(B3,"Pass") or Alarm(B3,"=Pass") or something else?

View Replies!   View Related
Excel 2007 Net Working Days Function
I am trying to use the NETWORKDAYS function in excel to calculate the working days for my company. the problem is I m located in UAE and here Saturdays and Fridays are official days off for my company. Now the problem is that the excel have this built in function that Sunday and Saturdays are weekend days. I want to customize the function for my local days off for my company.

View Replies!   View Related
Aggregate Function On Vlookup Array Not Working
I am trying to create a single cell formula that returns the min value of an array returned from a vlookup function. This is part of a more complex solution that I am trying to implement, but I think I have narrowed my problem down to this issue, so I have created a very simple example to demonstrate.

Cells A1 to B5 contain a lookup table:

table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}

LetterNumber D4 C3 B2 A1

Cells A7 to C8 contain the input values:

table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}..................

View Replies!   View Related
Validation And Lookup Function Are Working Weird
There is a range of quarters and relevant dates like from and till - means if you have 1 Q 2009 => it means from 01.01.09 till 31.03.09. I would like to have following in my excel. There is a validation for selecting Q from list, once you select Q, there is LOOKUP function displaying from and till dates. But however it's not working, in same cases it's working, but in most cases the from/till period is not relevant to selected quarter.

View Replies!   View Related
UDF Working When Called From Sub, Doesn't Work As Worksheet Function
I have written a user-defined function that searches for a small range within a larger range. The function requires two input parameters: the range you are looking for, and the range you want to look within.

For example, I might look for the string of values in cells A1:D1 in a larger range E1:H20. The function returns the row number in the larger range where the smaller range is found.

My problem is this: The function is working fine when I call it from another sub procedure. However, when I try to run it as a worksheet function, I get a "#VALUE!" error. The function pops up in the "insert function" menu, and it prompts for the two input parameters.

View Replies!   View Related
Reformatting Date Into Number. Reformat Cell Not Working. Function Maybe?
I am rewriting functions for my companies inventory spreadsheets. Excel for some reason took the item codes and formatted them into date format. Right now it is in 10/01/1902 format, and it needs to be in 10-1902 format. I've tried reformatting the cells but so far I haven't found one that works.

View Replies!   View Related
Macros That Were Working In Excel 2003 Are Not Working In 2007
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.

Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i

View Replies!   View Related
Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?

View Replies!   View Related
Function To Mirroring Cell Relative To Function's Location
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.

Is someone aware of a function which will target a relative location instead of an exact cell?

View Replies!   View Related
Nested IF Function Error (entered Too Many Arguments For This Function)
I keep getting the "You've entered too many arguments for this function" error.

Here is the formula:

=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))

View Replies!   View Related
Force Function To Use Row Relative To Cell Housing Function
I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).

My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.

My Script:

Function Position(rCell As Range, Optional RightPosition As Boolean)
Dim vResult

Select Case rCell.Text
Case "QB"
vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)
Case Else
vResult = "Invalid Position"
End Select

If RightPosition = True Then
Position = vResult
Position = "Position not valid"
End If

End Function

So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).

My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.

View Replies!   View Related
User Defined Function Function Tooltip
when you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?

View Replies!   View Related
2007 Right Function With Embeded Find Function
I have a range of cells, for this example I will use 2.

Cell E17 = 77/170
Cell E18 = 8/9

Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))

This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"

Now if I use:
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.

Using the formula:

View Replies!   View Related
Concatif Function Breaks Beforedoubleclick Function Just By Being There?
I have a tracking sheet (attached) that has many functions, but I'm having trouble with two of them. First of all, I have a Worksheet_Change event set that when a cell is cleared, it fills the cell with a formula to use the record above it as a default value if another cell is equivalent. This worked when I first wrote it, but now it seems to break every other time I use it. When assigning the formula it returns a Method Default/FormulaR1C1/Offset failed error message. It seems to be different for each one, each time and I can't figure out what the problem is.

The other problem I'm having is that I wrote a BeforeDoubleClick event to expand or contract any given record, or series of records. this too worked when I initially wrote it, but now is only hiding one row when it should be hiding eight or more.
I'm not terribly versed in VBA and totally baffled as to why I'm having these problems. As I side note, any ideas to clean up any of the code and make this run smoother (as ther will be many more series added when complete).

View Replies!   View Related
Show Insert Function Dialog Pre Set To Function
A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)

View Replies!   View Related
SUMPRODUCT Not Working For Me
what I want is quite simple actually. Worksheet 2 contains two columns where I want to search for certain text, when it finds the text in both columns (B & D) I want it to sum up the values in the same row on column F.

If I understood correctly there should be 2 ways to do this, using the SUMPRODUCT formula: ....

View Replies!   View Related
Working With Arrays
Below is my attempt to understand working with arrays of values.

Test() works fine. Yay! But it is "too easy" just plugging in a hard value for the range address...I don't always have that luxury. Normally I work with ranges that I've determined at runtime. I'd like to assign a range of values to an array but my attempt below -- test2() -- fails. I am not even sure if my effort is a good approach or not, but I know it doesn't work!

Basically, I am looking for how to assign the range of values to an array when I am determining the range in code -- as in:


Set Range = ~~


Range(Cells(1,1),Cells(x,y)) ~~................

View Replies!   View Related
Working With NamedRanges In VBA
This might be elementary, but I cannot get it to work.

I need to name range in VBA, work with the named ranges afterwards and change the names. I have split it into e couple of questions below.

1. Naming a range
By using 'Worksheets("Ark1").Range("C4").Name = Worksheets("Ark1").Range("C3").Value' I named cell C4.
This worked fine (but it is the most appropriate method?).

2. Retrieving a name
By using 'Worksheets("Arka").Range("C4").Name' I try to retrieve the name for cell C4, but this does not work for me. In stead of the expected name I get '=Ark1!$C$4'.
How do I retrieve the name (in this case 'Test')?

3. Renaming a range
The next thing I need to do is rename the cell. How do I go about that?

View Replies!   View Related
Trim Not Working
[data] ...

I am having problems removing "2009-642" from the above text string(s) (doesn't matter which)

This is just a sample line and it is not always in the same place so I am afraid a simple mid function from a set starting point will not suffice, the number is also dynamic in size, location and leading digits.

As a result I have created a formula to get rid of the "1 XS0444499197" part of the formula leaving me with " 2009-642 "

Now unfortunately I need to get rid of the spaces (or what look like spaces) just leaving me with the number but infuriatingly I can not get the trim function to work, so I can only guess that it is not in fact reading these spaces as spaces, and I'm at a bit of a loss how to proceed.

View Replies!   View Related
Create a column of concatenated values to serve as a "primary key" for a compare and merge tool (Synkronizer). For some reason, the CONCATENATE function isn't being recognized.

If I enter
instead of showing the strings located in A2 and B2 it displays
in the cell.

I tried creating a new spreadsheet and it works just fine. Does anyone have an idea of what might be happening? I have compared all of the options between the two spreadsheets and don't see any difference in them.

View Replies!   View Related
Hyperlinks Not Working
The following is a simplified example of my problem: I have a list of product item numbers in column A. Next to each item number in column B, is a hyperlink to a PDF file relevant to each item. Elsewhere in the sheet, is a drop down box containing the item numbers. Next to the drop down box is a HYPERLINK(VLOOKUP) that fetches the relevant hyperlink depending upon the selected item in the drop down box. Problem is, when I select the ‘lookup’ link, I get an error message ‘cannot open the specified file’ (the original Hyperlink works fine). Does anyone have a solution to this problem (preferably without resorting to Visual Basic?

View Replies!   View Related
Sub Worksheet_SelectionChange Not Working
I have popped the code below in the worksheet and it will not work on my machine (Vista + Office 2003). However, it works fine on my colleagues machine (XP + 2003).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 4 Then GoTo usevalue
On Error Resume Next 'MUST reenable events...
Application.EnableEvents = False
ActiveCell.Offset(1, 1 - Target.Column).Select
Application.EnableEvents = True
ActiveCell.Copy 'Put value into clipboard (marching ants)
ActiveCell.Interior.ColorIndex = 36
End Sub

I have tried different code from different web sources on different fresh files. This includes the Worksheet_Activate() function.

My macro security setting are low.

View Replies!   View Related
Working With Dimensions
Is there a way in excel to say if i select Item1 and it is 100mm x 30mm x 100mm, excel will say this will fit into a compartment called A1 and may also fit into a compartment called B6.

I have a list of all the compartments and what the internal dimensions are. So is it just a matter of saying if these dimensions are within those of the compartment then that item will fit in.

I have a list of some what 6000 Items that have dimensions. I may pick 200 or so of these items to fit into special compartments of a machine. Because of the varying sizes i need to know what compartment each item will fit in so i can build a machine big enough and get the right size compartments.

View Replies!   View Related
Adding HH:MM:SS Not Working
I have a column with hours in the following format HH:MM:SS, that looks like this:


I am trying to add the sum total of all hours, but its not working. I have my column formatted as time -> 37:30:55

View Replies!   View Related
COUNTIF Not Working
I am using the following formula to count the total number of contract types if 'ITD $K' equals '0' zero. But it returns 0 as output.


View Replies!   View Related
Working With Shapes
Where do I find a tutorial or examples to show me how to create bars for gantt chart in excel, based on date values?

View Replies!   View Related
Sendkeys Not Working
I'm trying to access one of my custom menus through a macro.

The keyboard commands are

alt, n, f


alt, n, g

I have these lines of

SendKeys "%nf", True
SendKeys "%ng", True

All this seems to do is put the letters f and g in cell A31!

View Replies!   View Related
Working With Percentages
I have a column of data with students marks ranging from zero to 78(S17:S74). I need to work out the mean score of the bottom 20% of all the scores achieved - Is this possible. I'm no expert so please keep it as simple as possible! I'm working with Windows XP and Excel 2002.

View Replies!   View Related
Get Vlookup In Vba Working
I have 3 sheets ("zip", "report", "ches") in a workbook

I want "ches" column A to be filled with data from "report" column D if there is a match of "zip" cell A12 from "report" column S

I keep getting the error
"unable to get the Vlookup property of the WorksheetFunction class"

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRange As Range
Dim myRange1 As Range

Set myRange = ActiveWorkbook.Worksheets("report").Range("A1:S65536")
Set myRange1 = ActiveWorkbook.Worksheets("ches").Range("a65536").End(xlUp).Offset(1, 0)

myRange1.Value = Application.WorksheetFunction.VLookup(ActiveWorkbook.Worksheets("zip").Range("a12"), myRange, 4)
End Sub

View Replies!   View Related
Vlookup Not Working
I have a vlookup that returns an #N/A error due to the table array originally being formatted as text. The data is simply a two or three digit number.

I have tried everything I can think of to get the table array to equal the lookup value.

I formated cell to number. I tried tab delimated/finish. I tried typing a 1 in a different cell and doing a paste special then multiply. I tried copying to a different worksheet. I just cannot get it to work.

View Replies!   View Related
What Row Is Code Working On
I'm using Excel 2007.

Is it possible to know which row code is active on whilst it is running?

s/s is 332324 rows and growing. When I run code which goes down one column and takes quite some time I don't know what stage it is at.

I'd like to be able to see on screen which row it is down to.

View Replies!   View Related
Not Sure Why CRLF Not Working
This should simply insert a linebreak correct? Cannot figure out why the following is not working :

UpdateFX = MsgBox("Update the current FX rates" & @CRLF & _
"[ 120 day history]", 65, "FX Rate Update")

View Replies!   View Related
Workbook_beforeClose Not Working In Add-in
I've the following code in my add-in. Strangely the code in the Workbook_Open event executes as expected, but not the Workbook_beforeClose event. May I know the reason?


Private Sub Workbook_Open()
msgbox "hi!"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
msgbox "bye!"
End Sub

View Replies!   View Related
IF Formulas Not Working
Here is what I would like to do:

Anytime there is a 1 in column B, I would like for columns G-I and columns L-N to display "N/A". I have tried all kinds of formulas, none of which will work.

View Replies!   View Related
Hyperlink Not Working
in my cell there is following formula/hyperlink:

=HYPERLINK(VLOOKUP($C6;Activities!$A:$AA;MATCH(D$5;Activities!$6:$6;0);FALSE);LEFT(VLOOKUP($C6;Activities!$A:$AA;MATCH(D $5;Activities!$6:$6;0);FALSE)

a) Clicking on it says "Cannot open the specified file."
b) I would like to define the format of hyperlinks to look different.

View Replies!   View Related
SendKeys No Working
Once the intended chain of actions is performed, normally EXCEL would go on to display the contextual menu, (which is its standard right-click action when no macros are involved).
Since I don’t want my macro operation to end with that menu covering part of my screen, I have the macro ‘type’ in ESCAPE, through one of these lines:

View Replies!   View Related
Sumproduct Is Not Working
=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),('Data Sheet'!E$2:E$997*IF('Data Sheet'!E1<=$F$4:$G$4,1,0))

The last part of the formula is the problem, the first two arrays are conditions and the last array is a sum if f4 a period of the year is equal or less to the data sheet tab it is summing.The formula below works fine, but when the last condition is added it doesn't work.

=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),'Data Sheet'!E$2:E$997)

View Replies!   View Related
AND Statement Not Working
Cells H5:S5 have a formula in it to determine whether or not the allocation calculation needs to be made. I figured it to be straight forward enough

View Replies!   View Related
OnError Not Working
OnError not working
Here's the code where I'm having problems:

View Replies!   View Related
VBA-code Not Working
I have the next VBA-

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("E:N")) Is Nothing Then
If Range("E34").Value = 0 Then
Application.EnableEvents = False
Range("G8").Value = Range("G8").Value + 1
If Range("J34").Value = 0 Then
Application.EnableEvents = False
Range("G9").Value = Range("G9").Value + 1
Application.EnableEvents = True
End If
End If
End If
End Sub

The code don't work. What's wrong with it?

View Replies!   View Related
Code Is Not Working
Function AH02(FIF, xi, di, zi, nt, TAI, ZMT, xi2, nr)

fit1 = Array(0.1352785, 0.02388504, -0.0007874863, -0.008694193, -0.01137302, 0.0004262445, 0.0001408564, 0.001013715, -0.00004032109, 0.006176128, -0.03644328, 0.001557726, 0.0483096, 0.01859556, -0.0008903489, -0.006407128, -0.001693491, 0.00008581065)
fit2 = Array(0.01184698, 0.01976108, -0.0008787826, -0.03195365, -0.01083098, 0.0005161274, 0.004048458, 0.001013387, -0.00005030517, -0.003262156, -0.003314432, 0.0001453592, 0.005082949, 0.001888962, -0.0000865367, -0.0006340434, -0.0001799523, 0.000008493003)
fit = Array(0.1352785, 0.02388504, -0.0007874863, -0.008694193, -0.01137302, 0.0004262445, 0.0001408564, 0.001013715, -0.00004032109, 0.006176128, -0.03644328, 0.001557726, 0.0483096, 0.01859556, -0.0008903489, -0.006407128, -0.001693491, 0.00008581065, 0.01184698, 0.01976108, -0.0008787826, -0.03195365, -0.01083098, 0.0005161274, 0.004048458, 0.001013387, -0.00005030517, -0.003262156, -0.003314432, 0.0001453592, 0.005082949, 0.001888962, -0.0000865367, -0.0006340434, -0.0001799523, 0.000008493003)

ermess = 0
t = TAI
L = 1
If (nr > 40) Then GoTo 900
10 If (nr < 1) Then GoTo 900
11 L = 2

View Replies!   View Related
Working Out Averages
I havea list of values that I need to work averages out for. Unfortunately, sometimes the values will be a zero and these should be ignored.

I can work out the averages easily enough using the average function, but can't quite figure out a way of ignoring the zeros. Sample data would be :-
week 1 - 3.42
week 2 - 0 (zero)

Using the standard average function, I get an average of 1.71 when I actually want it to have an average of 3.42.

I would prefer a function for this but would settle for a macro even though it would increase the program run-time.

View Replies!   View Related
Working With 3 Worksheets
I have 3 worksheets, 1st is called Rabbit Nov, the 2nd is called Badger Nov and the 3rd is called Tracking.

On the Tracking worksheet I am inputting the average scores taken from the other 2 worksheets.

So for instance on the tracking sheet I have E5=AVERAGE('Rabbit Nov'!E5:E21), then E6=AVERAGE('Rabbit Nov'!E22-37); E7=AVERAGE('Badger Nov'!E5-E18), E8=AVERAGE ('Badger Nov E19-E32)

So there are blocks of 2 Rabbit scores and then a block of 2 Badger scores.

What I want to know is, is there a quicker way of inputting the formulae or do I have to do cell by cell, as obviously automatically it doesn't work for this spreadsheet.

View Replies!   View Related
Copyright © 2005-08, All rights reserved