Variable Based On Inputbox

Mar 3, 2008

I have a macro that is designed to use an inputbox to ask for a number string that represents an entry on the current page, then go to that entry on the current page, and then select various info from that row and then use a portion of that string to move to another sheet which has the name equal to that portion of the string to perform more actions. My current problem is that when I try to set a variable based on a portion of the text input into the inputbox entry it gives me: runtime error 1004: method range of object _gloabal failed. And the line it errors on is the variable definition which is based on a portion of the inputbox entry.

Dim MyInput As String
Dim ChooseDate As String
Dim Hours As String
Dim SR As Integer
Dim ER As Integer
Dim SC As Integer
Dim EC As Integer
Dim RowVar As Integer
Dim ColVar As Integer
Dim found As Boolean
MyInput = InputBox(" Date-Time Number?")
If MyInput = vbNullString Then Exit Sub
ChooseDate = (Mid(Range(MyInput), 1, 4))

The "choosedate=" line is where it errors. It's suppose to take, as a string, the first four characters of the text input into the inputbox. Then it searches the current sheet for the entire entry, selects it, uses that row to collect more info and then moves to the sheet in the workbook that has the name equal to the first four digits of the inputbox text to do more actions. I'm sure it's a syntax error but my knowledge is at it's limits.

View 7 Replies


ADVERTISEMENT

Inputbox Value Can Be Compared To A String Variable Or A Numeric Variable At The Same Time

Dec 7, 2008

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 3 Replies View Related

Manipulating InputBox Variable

Oct 20, 2009

I have two inputboxes. I want to set the default value of the second inputbox as (effectively) one cell down from the first inputbox entry. In other words, if I type G17 into the first inputbox, I want the second inputbox to then have a default value of G18.

View 5 Replies View Related

Inputbox Button Control + Msgbox For Empty Inputbox

Jun 30, 2009

I have the inputbox so i can set a string value,
When the inputbox Cancle button is pressed i want to exit sub,
If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname.
If the inputbox has a value do >>>>>>That>>>>>

View 6 Replies View Related

Select A Variable Based On A Variable

Jun 18, 2009

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 9 Replies View Related

Progressive Sum Based On Variable

Jan 23, 2009

ok, this may be the same answer as the previous post i did, but if so, i can't figure that part out.

In O2, if M2 = 8, and N2 = 2008, then add O3 + 8 rows (O3:O10)

or, if M2 = 5, and N2 = 2009 then add O16 + 5 rows (O16:O20).

View 8 Replies View Related

Calculation Based On Variable

Jun 28, 2006

Im trying to access data from 2 different cells (say A1, B1), its for calculating transformer hotspots. The cooling method of the transformer is naturaly air cooled oil.. until the winding temp reaches 75C ( I have winding temp data laid out on a column) then the fans come on and some constants in the formula change (need to read from the other cell, say B1) so it switches, but the fans stay on UNTIL the winding temp is 50C and then go off, so the characteristics change back to the previous one, so I need to switch back. How do I go about doing this... my current method starts working when it reaches 50 instead of waiting the temp to go to 75 then fall to 50 then do it... im a bit confused.. maybe i shouldnt be doing this on excel... ow well

View 9 Replies View Related

Delete Row Based On Variable

Jan 3, 2008

What is the code needed to select and delete an entire row that is defined by a variable (for example, you want to delete row x which equals a specific number)

View 2 Replies View Related

AutoFill Based On Variable Last Row

Jan 20, 2008

What I need to be able to achieve is a function in my macro that will

a: Count the number of rows in the active sheet
b: Allow me to use this info in other functions, such as subtotals, autofills

After searching many sites I'm pretty certain the function I need to use is "rowcount".

I've managed to incorporate a loop using this function, which is fine when running the macro but not so good when you are stepping through 5000+ rows

RowCount = ActiveSheet.UsedRange.Rows.Count
For x = 2 To RowCount
Cells(x, 42).Select
ActiveSheet.Paste
Next x

So for me now it seems as though "x" should represent the number of row that has been counted in the above code. But when I try to use "x" in functions the macro falls over

Range("AQ2:AX2").Select
Selection.AutoFill Destination:=Range("R2C43:RxC50")

View 3 Replies View Related

Email To List Based On Variable?

Jun 3, 2014

I have a spreadsheet similar to the following.

Capture.PNG

I am looking for macro that would do the following: I would run the macro while in a cell in row 7 (the row may vary if I insert more data). It will find all of the zeros in the column and make a single "mailto:" link that includes each corresponding email address in column B.

So if I ran the macro while in cell E7, it would produce a link in that cell such as "mailto:tom@hotmail.com" and if I ran the macro while in cell F7 it would produce the link "mailto:tom@hotmail.com;kate@hotmail.com".

I started tinkering with some code on my own but wasn't sure how to compile the results into a single link.

View 4 Replies View Related

Different Calculations Based On Variable Inputs

Feb 15, 2009

If I input any FX pair (Column B) without ???JPY in it, then the formula in Column K produces the correct result. Formula is: eg: If Column B = EURUSD pairs and the like: =IF(C3="L",F3-E3,IF(C3="S",E3-F3))*10000.

This formula will produce a +ve or -ve result in points (PIPS) based on the values of E3 and F3. (eg. E3 = 1.2800 & F3 = 1.2750. If C3 = L then the result is -50. Conversely if C3 = S then the result is 50.)

When I input the JPY pairs in Column B, I need the formula to calculate the same way but multiply by 100 (not 10,000). This is because all non JPY cross pairs have 4 decimal places and JPY cross pairs have 2 decimal places. (eg. 1 PIP in EURUSD = .0001, 1 PIP in USDJPY = .01).....

So if Column B = USDJPY the formula would need to be: =IF(C3="L",F3-E3,IF(C3="S",E3-F3))*100 . Have tried many combination's of the IF function but am now starting to think this is not quite the right formula for this situation. Attached is a sample of what I am trying to achieve. The formula would need to incorporate any/all variations in currency pairs as listed in the attached sample.

View 2 Replies View Related

Auto Increment Based On ID Variable

Oct 3, 2013

I have a following the following dataset:

ID YearWk Sales
1 201310 $1000
1 201311 $1200
1 201312 $1300
1 201313 $1400
2 201310 $2000
2 201311 $2200
2 201312 $2300
2 201313 $2400

I'm trying to figure out how to create a new variable that will increment and reset based on ID.. similar to the FIRST. function in SAS.

Final Dataset:
ID YearWk Sales Count
1 201310 $1000 1
1 201311 $1200 2
1 201312 $1300 3
1 201313 $1400 4
2 201310 $2000 1
2 201311 $2200 2
2 201312 $2300 3
2 201313 $2400 4

View 3 Replies View Related

Value For Variable Based Off Control Checkbox.

Jan 26, 2007

I have several checkboxs in an excel sheet, that if one is checked i would like it to make a change to a variable in a macro that will run when saving a file.

for an example:

if checked:
Checkbox1 = NCE1
Checkbox2 = NCE2
Checkbox3 = NCE3
Checkbox4 = NCE4
Checkbox5 = NCE5

I have a bit of code that is like this....

ActiveWorkbook.SaveAs ("\marketing2PartageNonconforms" & sF1 & " - CO" & sF2 & " - FC" & sF3 & ".xls")
I would like sF1 to change depending on which checkbox is checked. so if checkbox1 is checked, than sF1 = NCE1 .

View 9 Replies View Related

Automatic Data Based On A Variable

Jan 3, 2008

I would like to enter a vacation code in E5 (V12) and have it automatically enter "VACATION DAY (12 HRS * $15/HR) in K5 and calculate the amount in L5. Of course, there are variables involved here:

V indicates that it is a vacation day.

12 indicates the number of vacation hours to be taken.

The $15/hr comes from the value in I5.

I don't know if this should be handled as a VLOOKUP along with other methods or if this is something that needs to be handled in VB?

View 9 Replies View Related

Change Number Of Zero's Based On Variable

Oct 19, 2009

I need the variable here to be 7 numbers long, if not I need to add zero's at the beginning. However the problem I am having here is when then number of zero's is greater than one.

Basically in the example below the variable 'Zeros' = 2 so I need the variable 'Variable' to give answer "00", how do I do this!?

If Len(ActiveCell.Value) 7 Then
Zeros = 7 - Len(ActiveCell.Value)
Variable = CStr("0") * Zeros
End If

View 10 Replies View Related

Create Hyperlink To Cell Based On Variable

Dec 8, 2012

VB:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="Work_Log!" & Sheets("Work_Log").Cells(b, 1), TextToDisplay:="View"

I want this to link to Work_Log!.Cells(b,1), however instead of grabbing the specific cell, it is pulling the contents of the cell and creating a URL of that. For instance if b = 1 and cell A1 contains the value "yellow" then this code is giving me the hyperlink to WorkLog!Yellow when I want it to read WorkLog!A1

View 2 Replies View Related

Function To Sum Number Of Rows Based On Variable

Jun 23, 2014

As you can see in the attached excel doc, I have calculated a column for "Number of Claims Per week" and also "Dollar amount Per claim". This was done using VLOOKUP and distributions. That is not the issue.

The issue I am having is creating the "TOTAL" for the week. So for example, if week one generates 4 claims, I need to be able to add up claim amount #1-#4.

It seems as though this would be a basic function, but I can seem to find a way to add up a certain number of cells, based on a variable generated from a random number.

If you take a look a my excel sheet, I am trying to solve for the "Claim Total" column on the far right. This column should be the sum of (Number of claims * The individual amount per claim). Note that if there is 4 claims in that week, I would have to add up claim 1-4 to get that weeks total.

Question2.xlsx‎

View 9 Replies View Related

Sorting Range Based On String Variable

Jul 31, 2014

I have a spreadsheet with column headers like, "Date" "Phone number" "Name" ect., and rows of data below. Each of these rows contains data pertaining to a prospect employee, as the purpose of the spreadsheet is to keep track of job applicants. One column is used as a 'Recruiter Sign-off' area, where the recruiter who processed the application inputs their name:

Example.png

I'm trying to automate a variation of excels sort function. However, I don't want to sort 'From A to Z.' Instead, I want to have the rows of data sorted with the 'Recruiter Sign-off' column used as the sort Key, and a selected name (of one of the recruiters) used as the sort Criteria.

I already have a userform that allows the user to select a name from a listbox. When they press confirm, the name is stored in a Public String variable, (selectedRecruiter). I'd like then like to have the rows below the column headers to be sorted based on the selected name. Example: User selects the name "Jon" from a list. The application then brings all of the applicants that 'Jon' has signed off on (those rows that contain his name) to the top rows of the range.

The workbook is shared, so I cannot use tables.

I found a code that accomplished what I want, however it also leads to a horrible system crash after a few times running it (BEX crash).

[Code] ..........

View 3 Replies View Related

Sumifs - Formula To Return Sum Based On 2 Variable

Sep 17, 2013

I am trying to work out a formula to return a sum based on 2 variable, but one of the variables in in a column and one in a row, anyway I can do this?

I don't want a normal sumif returning the relevant column as the relevant column will change based on a cell that can change.

I basically have a list of products sold (products listed down the page) by month (month listed across the page), I want to sum all the products in a particular month on a separate tab (both product and month can be changed).

View 8 Replies View Related

Append Variable Based On Date Ranges?

Oct 10, 2013

I have a reference dataset that looks like:

Adweek Start_Date End_Date
201201 05-Jan-12 11-Jan-12
201202 12-Jan-12 18-Jan-12
...

In the dataset I would like to merge the variabe Adweek, looks like:
Date Sales
05-Jan-12 $100
06-Jan-12 $110
...
15-Jan-12 $150
...

I'd like to get to here:

Date Sales Adweek
05-Jan-12 $100 201201
06-Jan-12 $110 201201
...
15-Jan-12 $150 201202
...

View 2 Replies View Related

How To Select Range And Clear Based On LR Variable

Jul 28, 2008

I want to select and clear a part of a sheet.

here is what I have.

Sub clear_data()
sheets("sheet1").select
lr = activesheet.usedrange.rows.count

Here is where I get lost in translation (syntax).

I want to select starting at Bcolumn through bycolumn but the row be set with the LR from above, since the rows always change.

I could write B2:by2000, but i want to use the LR variable to define the number of rows i have.

View 9 Replies View Related

Counting Unique Values Based On A Variable

Dec 15, 2009

I'm having difficulty trying to write a formula for the following type of data:

[data] ....

This is a data set with 10k+ lines; I have about 150 different items in the "Criteria" column. What I want to do is count how many unique items occur in "Column to be counted" by each vairable in the "Criteria" column. The output should look like this: ...

View 9 Replies View Related

Changing Vlookup Ref Column Based On Vb Variable

Jun 20, 2007

I am pasting vlookup formulas into a spreadsheet using a macro, and want to change the reference column number based on a variable generated within the code eg

= vlookup(RC1,table,i,false) where i is a predetermined variable in the code

for i=23, I need the result to be of the form

=vlookup(a1,table,23,false)

View 7 Replies View Related

Variable Range Formula Based On Cell

Oct 8, 2007

Here is what I would like to do: Have a formula like Max(B12:B14) change to Max(B12:B18) when I change the value in different cell from 3 to 7. I would like to stay away from the macro world and keep in formula world if that is possible.

View 6 Replies View Related

Lookup Based On Variable/Changing Sheet Name

May 23, 2008

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 9 Replies View Related

Sum Based On Multiple Conditions & Variable Range

May 30, 2008

I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached.
This has been driving me nuts for a few weeks now.

View 2 Replies View Related

Calculating Adjusted Factor Based On Variable Values

Feb 7, 2014

Here is a table with values from Factor1 to Factor 5. Underneath that, for a specific index, I can mark Y or N for factor values. For instance, for index 1, Factor1 and 2 is marked as Y. The goal is to calculate total factor based on variable and factor values. In this case, variable value is 6, factor 1 =1 and factor 2=1.25). So Total factor = 6*1.25*1.5 = 11.25. If all factor values are marked as N, then total factor = variable value (Example is for Index 3)

How this can be implemented. I tried using COUNT function to count the total number of Y but that works only if all factors have same values.

Table_factors.jpg

View 4 Replies View Related

Counting Number Of Unique Numbers Based On Another Variable?

Apr 24, 2014

I run a report that dumps from data from an ERP system. I've attached a sample of this report in excel that has two sheets. A summary and the data.

I am able to report on the total number of Purchase order lines and total purchase orders using various formulas. I am also able to use formulas to report on the number of lines associated with each product in the I column.

However I cannot, how to calculate the number of unique Po# (B Column) numbers based on the Product (I Column) .....

View 14 Replies View Related

Formula Needed Based On 2 Conditions And Variable Row Reference

Jan 9, 2014

I am an architect and recently I was required to do some extensive calculation relating to water requirements and toilet requirements for a building that i was designing. i created a excel file, with a basic vlookup function to put certain values in the cells that are pre-defined for a particular building typology (mostly created by governmental bodies as guidelines). for example, when i choose a particular type in column E, column F and G fills up automatically. now this table also gives me occupant load based on what is the area i enter in column D.

The problem is that I want to automate the calculation of toilet requirements. Now, each typology will have a different formula to calculate WC/Urinal/washbasin.

1. there will be 17 different formula - one for each typology (as shown in sheet 5)
2. Column j will calculate what is the number of WC required based on 2 criteria - first id what is the typology and then looking what is the number of occupant.

The problem here is for example, if in E9, selected typology as "offices", the formula of offices typology will be used in J9 and occupant value from H9 will be used but if i have E18 also selected as offices typology, the same formula will use occupant value mentioned in H18 instead of using H9.

Hence, excel will first have to look what is the typology selected. based on that, it will load formula - out of that 17 different formula - specific to that typology and finally it will refer to corresponding row for occupant load and will use it in the formula to get the result.

View 3 Replies View Related

Using Conditional Formatting To Fill Certain Cells Based On Variable?

May 23, 2013

The spreadsheet I've been working on is functionally working how I would like, but I am looking to improve it cosmetically. I have a range of cells in which I input scores. Those scores are averaged in blocks of the value of cell B1. I would like to fill the cells yellow if they are a multiple of the value of B1.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved