I experienced error "object required" when I tried to pass a argument of type variant into the b/m function call.
Call closeCon(con) 'con is of type Variant
The function is as follows.
Public Sub closeCon(ByVal nwAdoCon As ADODB.Connection)
nwAdoCon.Close
End Sub
I am assigning the result of a vlookup to a variable that is defined as Variant. I then need to use that variable in a calculation.
View 5 Replies View RelatedI have a multiple select list box which I capture the selected items by using ListBox1. List
I want to use the 'selection' to build up a string that will form a document name I want to add to an email as an attachment (all being done from Excel), however I get a type mis-match error as the ListBox1.List output is a variant.
So I'm aiming to have something like MyFile = firstpartoffilename & ListBox1.List & "*.doc"
Do I need to somehow convert the variant variable or are there some other clever tricks I can use to create my document name.
I have a button the runs Application.GetOpenFilename And puts the output filepath into a label. I then have another button that opens a inputbox asking to change the file name and then copys the file from above path to my destenation.
when setting the destination i use ThisWorkbook.Path & Application.PathSeparator & "Images" & Application.PathSeparator and the imputbox value as the filename. doing it like this it wont alocate a extention for the file ".jpg,ect". The label.caption has the .ext and i would like to be able to store everythink after the . as a variant.
Is there any way in VBA to convert a Variant into a String? This code doesn't seem to be valid:
Dim vName As Variant
Dim sName As String
vName = "John"
sName = CStr(vName)
Is/how possible to read entire ss into a variant "off-scree" without showing the user? I can open the file, I just dont know how to not show the file, select the entire sheet and read the values. I have seen the below but dont know how modify it as needed. Also is there a way to do this dynamically with variable length rows/columns?
Dim vaData As Variant
Dim lRow Long
Dim lCol As Long
vaData=ActiveSheet. Range("A1:B10").Value
from Professional Excel Development S.Bullen et.al. pp614
I want to test how many elements are not empty within a variant array.
For example for arrays Arr1 and Arr2 below the answers would be:
- for Arr1 not empty elements=1
- for Arr2 not empty elements=0
Arr1(0)=empty
Arr1(1)=2
Arr1(2)=empty
Arr2(0)=empty
Arr2(1)=empty
Arr2(2)=empty
I've tried with function COUNTA inside VBA but counts even the empty values:
Code:
NotEmptyCells = Application.WorksheetFunction.CountA(Arr1)
Is there another function to count this from an array or alternative way?
The answer is probably "because I'm stupid", but I really can't get my head around it! I'm playing with variant arrays for the first (and possibly last) time,
The code I have is:
Sub test()
Dim vSheetColours As Variant
Dim iCounter As Integer
vSheetColours = Range("Colours").Interior.ColorIndex
For iCounter = 1 To UBound(vSheetColours, 1)
MsgBox vSheetColours(iCounter, 1)
Next iCounter
End Sub
(Obviously this code doesn't do anything useful - but if I could get it to work, I might have a chance of making my real code work!)
Colours is a range of 8 cells. Each one has some text in, and has a different background colour. I'm trying to store the colours.
If I run this code, I get a runtime error 13 type mismatch, and it highlights
For iCounter = 1 To UBound(vSheetColours, 1)
But if I replace
vSheetColours = Range("Colours").Interior.ColorIndex
with
vSheetColours = Range("Colours").Value
it works fine.
Is it possible to use an array without looping through it? I have code that has two loops: One that is just for the array and the other that loops through the data. It would be nice if I could get it down to one loop, but I'm not sure if it's possible. For example, if you have
Dim varArea As Variant
varArea = Array(1,2,4,6,8,12)
For x = LBound(varArea) To UBound(VarArea)
Do While Not C Is Nothing
Code here
Is there a way to advance to the next item in the array without using the loop.
I am using strongly typed code, but I am also pulling data from worksheets into variants. e.g.
Dim MyArray As Variant
MyArray = [MyNamedRange]
is there any shortcut to convert the variant to a typed array, which doesn't just involve looping throught the variant and using a casting function, e.g.
Dim MyArray As Variant
Dim typedArray() As type
MyArray = [MyNamedRange]
typedArray = shortcut(MyArray)
I have looked into the maximum length of a variant/string in vba and it appears to be 250 characters. I am running a macro which first lists all excel files in a folder, returning them to a sheet, then using a loop statement opens each one in turn extracting the information to a second summary sheet before closing it. The file path to the folder is ridiculously long and the macro stumbles. I used the =LEN(A1) formula to check if the file names were too long for the string, but the maximum file name length was 226 characters. I've tried both String and Variant to collect the file names but both have the same effect.
View 2 Replies View RelatedI am trying to develop some code to serve as an 'undo' for several macros that I have. They each take a selected range and perform some changes to that range. I have managed to make some code that will undo the last macro run but would like to make something that can go back several steps. To that end, I have started with the following code but am running into an issue when I need to use a variant array to hold multiple arrays of a custom defined data type:
VB:
Type SaveRange
Val As Variant
Addr As String
[Code] .....
This declares some public variables I'll need, each as an array so that I can iterate through several steps of do/undo using the undoIndex. I then use them to save each range I am about to change by calling the following macro within my actual data-manipulating macros:
VB:
Sub Save_RangeForUndo(rng As Range)
Dim i As Integer
Dim cell As Range
undoIndex = undoIndex + 1
[Code] .....
If you look at the last line here, this is my problem; I can't figure out how to properly use an array to hold each instance of OldSelection() for later referral. Just for completeness, here is my actual undo macro:
VB:
Sub Undo_Operation()
Dim i As Integer
OldWorkbooks(undoIndex).Activate
OldWorksheets(undoIndex).Activate
[Code] .....
I need to get this OldSlctVariant()() array to hold each instance of OldSelection() so that I can restore them for each consecutive undo. I'm not very familiar with the variant type and anything more than the basic use of arrays.
I have about 20 different workbooks and i want to create identical tables and graphs in each one of them using the least number of steps.
The number of the rows changes by workbook but the colums are identical. So the range will vary by workbook.
so for example:
workbook1=
name sales
a 10
b 20
c 30
workbook2=
name sales
a 10
So I want a macro that does the tabling and charting in workbook1 and automatically moves to workbook2 and so on.
I would like to get the return value for an evaluate statement which is a variant data type, convert it to an integer and do some computation. I have 4 test programs, the first 3 all work, the 4ths does not.
The first sd_test(returns 1) and st_test1(returns 1) demonstrate that my evaluate statement is correct and works,
sd_test2(returns 3) shows that I can convert a variant to an integer
in sd_test3(returns #VALUE!) I try to put both together and it doesn't work?
I wanted to convert a variant to an integer. The variant data is stored in an array, the variant data was entered from a userform.
Private Sub CB_OilPrice_Click()
Dim xx As Long
UserFormSpecifyDetail.Show 'Enter data
For i = 1 To 120
xx = VarType(UserFormInputArray(i)) 'Returns 8
xx = Int(UserFormInputArray(i)) 'Returns error
Next i
End Sub
The value in UserFormInputArray = "50000"
error = Run-Time error 6, overflow
Excel 2003
I'm going wrong with this formula: =if(iserror(J3-VLOOKUP (H3,$H$730:$J$1456,3,FALSE)," "(J3-VLOOKUP(H3,$H$730:$J$1456,3,FALSE)). I feel like I've used a variant of this formula before without encountering any problems.
View 3 Replies View RelatedIn cells BS8 I have the following =100*SUBSTITUTE(BR16,"metres","") to get centremetres from cell BR8 and in cell BT8 I have the following
=IF(BS8,ROUND('Under 6 Boys'!D11*(BS8-'Under 6 Boys'!E11)^'Under 6 Boys'!F11,0),"") I get a #Value error
I have also tried
=ROUND('Under 6 Boys'!D11*(BS8-'Under 6 Boys'!E11)^'Under 6 Boys'!F11,0)
In BT8 but I get same error
I have used it in another sheetsheet and it works I don’t understand why is is happening ...
I am using a combination of validation, vlookup and simple formulas to lay out a workorder. If I leave something blank in one of the feilds then everything that comes after say #n/a and won't allow the calculation to complete. Is there a way I can set a default value or something so to make calculate all the feilds that ARE filled in?
View 9 Replies View RelatedI have a workbook with data tabs (one shown here) and a summary tab. Essentially, one inputs hours for people in the Data tab (Tech) that are then calculated/summed to total $ based on rates I have hidden elsewhere in the workbook (not included). I'm trying to bring the subtotal lines (highlighted green) into the Summary tab based on which subtotal and what month/year it is. My formula works fine, except for the fact that it is returning a #Value! due to the "Hrs" heading for each Phase. I don't know why it's doing this because I've not run into sum array issues when mixing numbers and letters before. The error formula is highlighted in yellow in my attached sample, but below is a copy of the formula.
View 10 Replies View RelatedI can not get the following formula to work - I keep getting #Value errors and I've checked the fields and the values are correct
=SUM(IF(Input!$A$6:$A$4006=1,IF(Input!$I$6:$I$4006="DM",IF(Input!$K$6:$K$4006="Bid",Input!$L$6:$L$40 06,0),0),0))
My intension is that if A=1 and I=DM and K=bid then add the corresponding values in L and display. I can't figure out why this formula in another cell and works fine
=SUM(IF(Input!$A$6:$A$4006>0,IF(Input!$A$6:$A$4006<1,IF(Input!$I$6:$I$4006="DM",IF(Input!$K$6:$K$400 6="Bid",Input!$L$6:$L$4006,0),0),0),0))
it is just checking an additional condition of the value in column A
I am using a SUM function with multiple conditions as an array formula.
View 14 Replies View RelatedI am creating a spreadsheet which shows the date of when the training was cpmpleted. I have added an expiry column which gives the date 3 years from start date no problems. The trouble i am having is that the column will return an #VALUE when no training date is entered. ie: the person has had no training so no date can be entered.
I've been using some code I wrote at the start of March to open some files that I've created earlier in the day in order to add to them.
The code worked fine until the month changed. Here is the code I have to open the file I need.
strdate = Format(Now, "dd-mmmm-yyyy")
ChDir "C:DesktopTodays ReportsReports " & strdate & ""
Workbooks.Open Filename:= _
"C:\DesktopTodays ReportsReports " & strdate & "Date Report " & strdate & ".xls"
Since April 1st I have been getting a compile error saying I have the "wrong number of arguments or invalid property assignment".
I am at a loss as to why the code doesn't wok as nothing chaged between March 31st and April 1st.
Having a strange problem with vLOOKUP. Please see the attached file. The result is in cell M1 of the sheet (Link to the file below).
Vlookup works fine if the data is entered manually in cell H1, but with the formula it errors up .how i could make this work with the formula?
Sub indX()
For rwindex = 1 To 4
For collindex = 1 To 10
With Worksheets("sheet1"). cell(rwindex.collndex)
If Value < 0.001 Then .Value = 0
endwith
Next collndex
Next rw
End Sub
I have a code here and i have to spot what the errors are.
I know there is an error with 'endwith' but not sure what to do to fix it
What i'm looking to do is input a VBA command to comb the activesheet (which contains approximately 1400 rows, and 32 columns of mixed data and formulas), and determine if the text value "#N/A" appears anywhere on the sheet. If it occurs one or more times, i want it to set a boolean flag to true.
I know of ways to do so by setting a = countif(A:AG,"#N/A") formula in a cell, but unfortunately this is not my solution here, as it needs to be done before the formula is converted to text. A countif does not show a visible value of #N/A if its true value is a formula.
I've got a bunch of formulas that often are trying to divide by zero, hence this error in the cell. Is there an easy way to modify the forumla or format so that if a division by zero is attempted, the cell can show a zero instead of this error?
View 4 Replies View RelatedWhen I run a report, for some reason our system imports certain numbers which have errors, as a consequence when I try and do a pivot table I get a lot of returns showing #div/o!. If you look at the spreadsheet the errors start on line 103 sheet 2 and finish on 2631. Sometime the report can be huge and takes a while to go through these manually to convert to numbers.
View 3 Replies View RelatedI have the formula =today()-g3 which works fine to let me know how many days a loan has been out. However when there is no date in column g i get #value! errors. Can anyone reccomend an alteration to the formula to avoid these errors showing up as they dont look great.
View 2 Replies View RelatedThe following formulas is showing error in some cells?
=IF(ISTEXT(PRONOSTICOS!G5), PRONOSTICOS!G5, "")