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


Advertisements:










Double Lookup (lookup Variable Row And A Variable Column)


I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this.

Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this?

Or in the attached file, I would like to say A and 15 and get A15.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Index Match Lookup, Variable Return Column
I would imagine that the title of this thread made little sense, so let me try to explain:

Sheet A has columns A-C populated with data. Column D needs to search Sheet B columns A-C for a match. (ie, see if the data on sheet A is also on sheet B) if this is true, then it needs to return the value of the LAST column of the matched row in Sheet B.

So, if Sheet A R1 A-C is 1,2,3. Sheet A R1 D needs to search Sheet B for 1,2,3. and when it finds it, (for example on row 9) return the value of the last column of Sheet B row 9. The issue is, the last value could be in column R or S or AA, there is just no way of knowing.


View Replies!   View Related
ODBC Lookup To Populate Variable
I have no problem setting ODBC connections in vba, that return the results to a selected Range destination, but short of then setting txtVariable = Range("A1") is there any way to directly tell the ODBC connection to set the result of the query to txtVariable?

View Replies!   View Related
Lookup Function For Variable Amounts
I have a worksheet where I only want to pull over details for rows that have a amount listed in the variance column. Is there some type of macro I can create to perfrom this for me? There will be many rows that I do not need to pull, but for the ones that have an amount listed in variance, I will want to pull over certain cells from those rows.

Is there any way excel can do this for me?

View Replies!   View Related
Lookup Based On Variable/Changing Sheet Name
I'm trying to figure out a way to lookup a value based on a conditional sheet name. I'm finding it difficult to explain in words what I'm trying to do, so I'm going to try and describe it in a miniature example of my spreadsheet. I apologize if this is going against forum rules, this seems like the quickest way to get my question across:

The following is the way my spreadsheet's first page is set up:

ID Apr-08 Mar-08 Feb-08
1
2
3

The column labels depend on another table, which changes monthly.
The rest of the sheets (24 of them, labled "200804", "200803", etc) look like this:

ID $ amount Date paid
1
2
3

I have another table upon which the na

I'm trying to return the "Date Paid" value for each ID for each month. Each month, however, more data arrives, and the labels change. I have several dozen spreadsheets formatted in this way, and I'm trying to avoid having to change the vlookup (or other formula) manually each month for each sheet. Is there a way to make the vlookup depend on a table?

This is essentially what my formula looks like right now for cell A2:
=VLOOKUP($A2,'200804'!$A$1:$G$10000, 3, FALSE)

If I could replace '200804' with a cell reference, that would be ideal. I haven't been able to figure out how (if possible); I tried experimenting with index sheets but that went nowhere.

View Replies!   View Related
Assigning A Variable And Pasting Variable To Last Unused Column
to assign a variable to equal a Constant variable, then I need to find the last unused row on the worksheet, then paste that variable down the column (1-12200 or so rows). I also need to assign Strings for the first two Rows in the target column.

View Replies!   View Related
Autofill With Range That Is Column Variable And Row Variable
I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this

View Replies!   View Related
Lookup Table (adjust Which Column The Lookup Function Refers To)
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.

View Replies!   View Related
Double Lookup For Name/Value
I have a list like this:

xxxxxxxxxx1-jan | 2-Jan | 3-Jan
-------------------------------------------------
Location 1
Bob Smith ----B2--- C2 ---- D2
Frank Thomas-B3---- C3---- D3
Location 2
Bill Clinton----ETC ALL
George Clinton

And another sheet, a similar list, (just names and randomly sorted) but populated with the values I want,

xxxxxxxxxxx1-jan | 2-Jan | 3-Jan
-------------------------------------------------
Bob Smith --- 2 -----5------ 1
Frank Thomas 3----- 1 ------2
Bill Clinton ----4----- 5 ------2
George Clinton 4----- 4 ----- 4

I want to check the name in A2 on the first sheet against all of column A on the second sheet, then find the number value for the correct day for that name.

How can I write a formula that will look up these values for me? I looked at OFFSET but unfortunately I don't have a lot of excel experience (yet)


View Replies!   View Related
Double Lookup
=INDEX('sqft - PC each bldg'!D2:D1705,MATCH(Sheet5!B3&Sheet5!F2,'sqft - PC each bldg'!A2:A1705&'sqft - PC each bldg'!C2:C1705,0))

my result is N/A

I wonder if it has to do with both lookup ranges being columns?

View Replies!   View Related
Double Lookup Values
I am trying to lookup the value that corresponds to three sets of data. The formula I have been using is but I am still getting #NA. Iíve attached the spreadsheet

=INDEX($B$3:$I$17,MATCH($B21,$A$3:$A$17,0),MATCH($A$22,$B$1:$I$1,0)+MATCH($B$20,$B$2:$E$2,0)-1)

I am looking to search Vendor name 1, Actual and A to return the value of 1.

View Replies!   View Related
Macro: Concatenate Text, Variable & Double Quotes
I need to write a line of code which puts the following into a cell:

=+C4 & " - " & 1

The cell reference at the beginning will always be C4, however the "1" at the end will be variable named "GroupNo"

So my code will be something like:

Range("B46").Value = "=+$C$4 & " & "" - "" & "GroupNo"

This isn't working. I've tried every variation of what it should be but I keep getting errors like "type mismatch" or just the wrong thing being put in the cell.

View Replies!   View Related
Double Lookup From Two Dimensional Table
I am trying to get a cell in my spreadsheet to look up a value based on two values. I have a dropdown list that lists the worksheets in the workbook, and each worksheet has a table with width measurements for the columns and height measurements for the rows. I have a function that is mostly working, it calls the data from the proper worksheet, but it rounds the measurement values down, and I need it to round to the next highest value on the table.

For instance, the measurement may be 55" x 55" in, but the table has values for 54" and 60". The current formula rounds down to the 54" measurement, but I need it to round up to the 60". I have attached what I have so far with further notes and cells highlighted.

View Replies!   View Related
Double Lookup And Pull Adjacent Cell
I need is spread across various rows. In a separate sheet I want to be able to lookup two identifiers and pull the number needed in the adjacent row.

The first identifier is a 3 letter character, the second is CURREVO and I need the number to the right of CURREVO. My problem is CURREVO is not always in the same column, but in relation to the 3 letter character is always in the same row.

A B C D E F GDMGCURREVO52011.25YTDREVO243085.00DTYCURREVO11892.50YTDREVO59783.50ECUCURREVI1943.00YTDREVI 5,541.25CURFRQI4.00EEGCURREVO32864.75YTDREVO205426.75EICCURREVO658761.26YTDREVO3507022.68EILCURREVO335741.70YTDREVO1720830.72ENTCURREVI161242.39YTDREVI638681.84CVLCURREVO796266.21YTDREVO4816890.98ENDCURREVI34479.19YTDREVI44074.54EYECURREVI11880.12YTDREVI 108,007.02(Null)MICCURREVO1098694.15YTDREVO5766072.54NEUCURREVI25251.90YTDREVI 158,236.60CURREVO207.00

i.e.
in a seperate sheet, I want to pull NEU, in that row I want to find CURREVO and bring back the adjacent number = 207.00

View Replies!   View Related
Double Lookup Formula To Work With A Table
I am having trouble getting the double lookup formula to work with a table. See attached sample.

View Replies!   View Related
Double Lookup Function: Find Total Invoices Billed In February For ABC Consulting Company
I have a spreadsheet in which I am trying to track invoices billed according to month in question. For example, I need to find total invoices billed in February for ABC Consulting Company. I have a database in the same spreadsheet that contains all invoices billed for an entire year for all companies. How do I pull invoices for a particular month only, in this case for the month of February? I have attached an example of spreadsheet in question. Included is a tab which indicates desired results.

View Replies!   View Related
Set Variable: Object Variable Or With Block Variable Not Set
I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"

Sub test()
Dim StartRng As Range
Dim Buffer As Range

Set StartRng = WorkSheets("Sheet1"),Cells(1,1)

StartRng.Activate
ActiveCell. CurrentRegion.Select

Buffer = rngStart.CurrentRegion.Copy

' I also tried the following line of code but that didn't work either
'Set Buffer = rngStart.CurrentRegion.Copy
..
...
End Sub

View Replies!   View Related
Lookup Value In Column & Lookup Value For Offset Column
I am trying to find a formula that would pick out a row from a matrix based on the values in the first column and then return to me each of the subsequent values from that row. I can't do a vlookup because there are a lot of blank cells and which columns have data changes. I've attached an example. The text in A1 will change and cells A3:B5 will show the corresponding values from that row along with the column title. I would prefer a formula to type in and leave in cells A3:B5 but if that can't be done VBA is okay too.

View Replies!   View Related
2007 Macro: Run-time Error 91:Object Variable Or With Block Variable Not Set
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...

The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.


View Replies!   View Related
Insert A Variable Number Of Rows And Copy And Paste From And To Variable Positions
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.

View Replies!   View Related
Macro Error Message (object Variable Or With Block Variable Not Set)
When i try to run the code below i get the error message - object variable or with block variable not set-

Sub REFRESHXX()

'LIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1, Criteria1:="1"
'SET RANGE
Dim sFormula1 As String
Dim sFormula2 As String
Dim sCell1 As String
Dim sCell2 As String
Dim sSheet1 As String
Dim sSheet2 As String
Dim r As Range
Dim MyRange As Range 'for testing

With Sheets("Points")
sFormula1 = .Range("CY1").Formula
sFormula2 = .Range("CY2").Formula
End With

'FORMULA IN R1C1 STYLE
strFormula = "=IF(ISNA(VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE)),0,VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE))"
'ENTER FORMULA IN ALL CELL RANGES
r.FormulaR1C1 = strFormula
'REDUCE TO VALUES
Dim ar As Range 'an area is a range
For Each ar In r.Areas 'areas are discrete, contiguous ranges of cells
ar.Value = ar.Value
Next ar

'UNLIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1

End Sub

View Replies!   View Related
Find Dates Macro: Object Variable With Block Variable Not Set
I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then End
startDate = Format(startDate, "dd/mm/yyyy")
stopDate = Format(stopDate, "dd/mm/yyyy")
startRow = Worksheets("sheet1").Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select

End Sub

View Replies!   View Related
Inputbox Value Can Be Compared To A String Variable Or A Numeric Variable At The Same Time
I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.

View Replies!   View Related
Find Method Code: Object Variable Or With Block Variable Not Set
I need my program to:
- find the cell containing the string "Datum/Tid"
- record the column and the row of the found cell in two variables lCol and lRow

Here is my

Sub test()

Dim rFoundCell As Range
Dim lRow As Long
Dim lCol As Long

'Find method of VBA
Set rFoundCell = Range("A1")
Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

'for anyof the two lines down I get the message "object variable OR block variable not set"

lRow = rFoundCell.Row
lCol = rFoundCell.Column

End Sub

View Replies!   View Related
Runtime Error 91, Object Variable Or With Block Variable Not Set
This works fine in Excel 07 but when ran in 03 it doesn't work and I get that error message.

Sub mcrRefresh2()
Sheets("WeeklyData").Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets("WeeklyData").Range("aa1").ListObject.QueryTable.Refresh BackgroundQuery:=False
Worksheets("WeeklyPivot").PivotTables("PivotTable1").PivotCache.Refresh

End Sub

View Replies!   View Related
Debugging Error: Object Variable Blocked Variable Not Set
I am getting an error at this line:

Set rng5 = Range(.Cells(celle2.Row, celle4.Column), .Cells(celle2.Row, celle4.Column))

Sub UpdateNumbers()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim celle1 As Range
Dim celle2 As Range
Dim celle3 As Range
Dim celle4 As Range
Dim celle5 As Range
Dim flag1 As Long
Dim flag2 As Long................


View Replies!   View Related
Object Variable Or With Block Variable Not Set Error With Macro
I attempted to modify "macro_1a1ay" to look into the "comments" sheet (column a) for a specific text string. If that text string is found, I have it delete the entire row, then re-sort the page and return to the calling page. It works well as long as it finds something. When it does not find the string (i.e. like now when the page is blank) it gives me the run-time error mentioned above.

View Replies!   View Related
Run Time Error 91 Object Variable Or With Block Variable Not Set
I am having a lot of trouble finding out why I am getting error. I believe the error is because it can't find the number. In cells C115:C314 i have the numbers 1 to 200 in order. when someone types in 1 to 9 in the text box it works, but on 10 and over i get the error ???? here is the code I have

shCalculator.Range("C115:C314").Select
Selection.Find(what:=CInt(txtPackageID), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Select
x = ActiveCell.Row
shCalculator.Range("ProposedMeter").Value = Cells(x, 7).Value
shCalculator.Range("Package").Value = Cells(x, 12).Value
shCalculator.Range("ProposedMeterAmount").Value = Cells(x, 30).Value
shCalculator.Range("Term").Value = Cells(x, 62).Value
shCalculator.Range("Discount").Value = Cells(x, 67).Value
shCalculator.Range("Equipment").Value = Cells(x, 72).Value

View Replies!   View Related
String Variable To Call Defined Range Variable
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?

I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.

View Replies!   View Related
Find Method: Object Variable Or With Block Variable Not Set
I have a spreadsheet form with all kind of values and what I want to do is to find a data in a worksheet named "Config" based on the spreadsheet activecell value. With the find row adress I return data of another column but same row (in the worksheet) in two diferent label captions. If the spreadsheet activecell value it was found in the worksheet everything is ok but if the value is not found I'm getting the message "Object variable or with block variable not set"....

View Replies!   View Related
Run Time Error 91, Object Variable Or With Block Variable Not Set
Function Find_Range(Find_Item As Variant, _
Search_Range As Range, _
Optional LookIn As Variant, _
Optional LookAt As Variant, _
Optional MatchCase As Boolean) As Range
Dim c As Range
If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
If IsMissing(MatchCase) Then MatchCase = False
With Search_Range
Set c = .Find( _
What:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _ ........................

Using the message box I see that sheet 1 opens but then I receive an error message Run Time Error 91, Object Variable or With Block Variable not set. I tried declaring and using set on "project" but got nowhere. I also need to have a message indicating project not found. Once this part is solved I will loop all of my other workbooks

View Replies!   View Related
Error: Object Variable Or With Block Variable Not Set
I've set up a userform (frmCoC) to read and write info to the "contractor database". Within that userform is a listview control that reads off a second, "induction database".

When the user selects and clicks on one of the displayed entry in listview, another form (frmInd) opens to edit data on the induction database.

There's a command button along side the original form (frmCoC) to create a new entry in the induction database. The click event for the button is as follows:

View Replies!   View Related
Error 91 Object Variable Or With Block Variable Not Set
At a high-level: My code fails in the sub-routine that is called (SecondSub). The first line of code on the SecondSub is where I get the Error 91 Object Variable or with block variable not set.

1st Routine:
________________________________________________________
Sub FirstSub()
Dim ie As Object, iebody As String, strURL As String, strUsername As String, strPassword As String
Dim lRow As Long
Dim abc As String
Dim striEst As String
Dim ele As Object
Dim LinkHref As String
Dim a As String
Dim b As String
Dim c As String
a = DateAdd("d", -1, Now)
b = DateAdd("d", -2, Now)
c = DateAdd("d", -3, Now)

Set ie = CreateObject("InternetExplorer.Application")
strURL = "website"
strUsername = "xxxxxxxxxxx"
strPassword = "xxxxxxxxxxx"
While ie.busy
DoEvents
Wend......................


View Replies!   View Related
Error - Object Variable Or With Block Variable Not Set
I've got a simple Macro which manipulates data in various worksheets. Problem is that I get the error: "object variable or with block variable not set" in the area of the code that I have put in bold below:

All I'm trying to do in this part is find the first occurrence of the #n/a value ....

View Replies!   View Related
Global Variable Or Pass Variable Between Sub-routines
The first goes through a directory and opens all the files.. after it opens a given file it goes off into a sub-routine to process the data in that file.

I am trying to create a counter in the first sub-routine and then pass that value into the second sub-routine to tell it to put the values out on the next row down.. so the first time through it puts the values out on row 1, next time it puts them out on row 2, etc.


View Replies!   View Related
Save Average Of A Variable Selection To A Variable
I have looked at many different examples of uses of the average function but I haven't found any examples of what I need it to do. Here is the code I am trying to use, but I am getting some errors.

View Replies!   View Related
CreateObject: Object Variable Or With Block Variable Not Set
I have my below code which will create a looping "ftp" and "get" effect.
However i am having some problems right now. There's always an error that states "Object variable or With block variable not set".

Sub File_Transfer_testing()

Dim N As Integer
Dim Lotid As Variant
Dim Tester_array(1 To 15) As String

Tester_array(1) = "10.10.10.10"
Tester_array(2) = "20.20.20.20"

Lotid = InputBox("Input a LOT ID")

N = 1

Set fs = CreateObject("Scripting.FileSystemObject")
Set A = fs.CreateTextFile("C:script.txt", True)
For N = 1 To 2
A.writeline "open " & Tester_array(N)
A.writeline "xxx" 'username
A.writeline "xxxxxx" 'password
A.writeline "cd /zzz/reports"
A.writeline "prompt"
A.writeline "ascii"
A.writeline "mget " & Lotid & "*sorts" 'file to be uploaded
A.writeline "quit"
A.Close

Set fs = CreateObject("Scripting.FileSystemObject")
Set A = fs.CreateTextFile("C: upload.bat", True)
A.writeline "ftp -s:C:script.txt" 'the ftp site
A.Close

dRetVal = Shell("C:upload.bat", 0) 'upload the file
''''''''''end upload.bat file
Next
MsgBox ("Done")
End Sub

View Replies!   View Related
Fastest Lookup Method: Use A Key To Lookup A Value (VLookup, Index/Match, DGet, And The Rest)
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?

View Replies!   View Related
Tabled Lookup: Able To Lookup If Anywhere In A Cell Contains A Word From A List Of Words, And Then Provides An Output
I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.

Column G:
VAT payment
HMRC payment
Pay VAT

I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC

ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.

View Replies!   View Related
LOOKUP / MATCH Function To Lookup The Owner Name Typed In Cell
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.

For example, the data source contains different pets, their names, ages and their owners.

The other sheets are on a one-per-owner basis.

What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:

John Smith (in cell A1)

Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4

View Replies!   View Related
LOOKUP With AND Requirements (lookup A Particular Student's Grade In A Particular Assignment)
I'm making my own gradebook (attached) and one of my sheets will list scores for each student in different assignments. I have one sheet which keeps track of all students and all assignments with other info. I would like to program cells in one sheet (the third in the attached file) to lookup a particular student's grade in a particular assignment. I figured trying a LOOKUP with an AND requirement might work but it keeps returning the message "could not find value".

My formula references the student's name and the assignment from the identifying cells so that it is easy to copy and paste. I wondered if it was this which resulted in the error, but doubt it.

View Replies!   View Related
Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.

I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.

View Replies!   View Related
Select A Variable Based On A Variable
On my userform I have 20 comment icons (imported pictures), that when clicked need to bring up an InputBox for the user to add a comment, and store that comment in a Public variable specific to that comment which will later be written to the spreadsheet.

To keep it simple, lets say I have two comment icons to click, one to add comments to the "Testing Completed?" field, and one to add to the "Sign-Off?" field.

The first comment icon is named TestCompIcon, the second is SignOffIcon, and the public variables they write to are called TestCompComment and SignOffComment respectively.

To avoid having to code the InputBox procedure for every comment icon on the userform, I was hoping that upon click, the icon would call a centralized routine that would establish the name of the variable that needs to be written based on the name of the icon comment that was clicked. Something like as follows:...............


View Replies!   View Related
How To Reference A Variable Stored Within Another Variable
how to achieve what I'll call "parameter driven" code where the "parameters" are variable names.

For example, in the following code... I'm looking for an answer to the question posed...

Sub Test_Sub()
Dim vX, vY, z
vX = 10
vY = "vX"
For z = 1 To 3
'This next line generates a Run-time error... But...
'=====================================
MsgBox z * vY '

View Replies!   View Related
Object Variable Or With Block Variable Not Set
I have created a form, that users will use to input data. I want to provide a template excel workbook, that they are supposed to copy, and save with their own file name leaving the template untouched. The following code is supposed to check to see if this is the template workbook, and if so, it will open an worksheet (otherwise hidden) to allow them to do a "save as". Everytime I try to run the code I get the following error..."object variable or With block variable not set" and I don't know what I'm doing wrong. The only thing I did find was if I removed the "unload me" I didn't get the error. However, I need the form to unload so that the user can save the file under a new name.

View Replies!   View Related
Variable Sheets, Variable Input
I run a machine that scans parts and is able to output the scans into Excel. Each part scanned creates a new sheet, and the number of sheets is variable as the number of parts scanned depends on the size of the lot. Also, since each part scanned is going to have a different number of features, the information being output on each sheet will be variable as well.

I am trying to figure out how to write a macro that will find the first feature, find the values for that feature I am looking for, output a max and min into a final sheet, and repeat for each feature, and for each sheet. I hope this is clear.

View Replies!   View Related
Object Variable Or With Block Variable Not Set..?
I have used this type of thing a few times before with no problems, but now I am getting the error in the thread title on the cells.find line:

View Replies!   View Related
Object Variable Or With Block Variable Not Set ...
I am trying to call one webMethods service from excel. The code is generated by webMethods. I can compile the code without any error. But while running the code I am getting this error "Object variable or with block variable not set".

Option Explicit
Private wc As Context
Private CError As String
Private outputs As New Values
Private isConnected As Boolean
Public Function TestWebServie(in_name As Variant) As String
On Error Goto Err_TestWebServie
Dim inputs As New Values
If isConnected = False Then
CError = Connect
End If
If Len(CError) = 0 Then
inputs.put " name", in_name
setStatus ("Invoking service Default.SukantaTestWebServie...")
Set outputs = wc.invoke("Default.Sukanta", "TestWebServie", inputs)
Else
TestWebServie = CError
End If
clearStatus...........

View Replies!   View Related
Set Range Variable Using Variable Row Number
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.

Dim Totals As Range
Set Totals = [U37: AE37]

Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.

View Replies!   View Related
Lookup Function To Lookup For Data In Another Table
I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.

View Replies!   View Related
Lookup Data Left Of Found Lookup Value
here is an example....

(this is on a sheet called Summary)

----A--------B --------C------- D
1Names----At Bats----Hits----Batting Average
2Tom-------38--------31------.816
3Derek------19--------14------.737
4Joey-------40--------28------.700
5Chris-------40--------27------.675
6Chuck------37--------24------.649

Using the LARGE function, Excel has created a list based on batting averages(on a separate sheet called Line-up). It looks like this.....

---A----------B
1Names----Averages
2 -----------.737
3 -----------.700
4 -----------.675
5 -----------.816
6 -----------.649

The problem I am having is figuring a way for Excel to also bring the corresponding names (after using the LARGE function to create the line up list).

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved