Use IF And AND Functions To Create A Basic Input-output System

Nov 18, 2009

I'm trying to use IF and AND functions to create a basic input-output system. The problem I have (more details below) is the 64 limit when the potential inputs could be infinite.

For values entered into a cell between 1 and 10, this should produce an output of 0.00 in another A value higher than 10 should output as 1.00, and from then an extra 1.00 is added for every '10' added. There is no limit on the input ie 1 = 0.00, 11 = 1.00, 29 = 2.00 and so on.

View 2 Replies


ADVERTISEMENT

Basic Functions

Mar 28, 2007

I used to use VB and VBA years ago for fun, now I have a project at work and I havent touched it since 1999 at least. I am trying to program a function that I can call and pass it a text variable. It will search through excel for that text, and return a cell as a Range. Thats where I run into problems. Heres the

Function lookFor(text As String)
lookFor = Columns("B:C").Find(text, After:=rCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Address
End Function

'...then later in the code...

Private Sub search_idBTN_Click()
'...
Dim rCell As Range
Set rCell = lookFor(searchTXT)
'...
End Sub

I think it has something to do with the variable type, its expecting a range and it gets a string or vice versa or something.

View 2 Replies View Related

Transferring Of Input From Input Cell To Output Cells?

Jun 8, 2014

I would like to know if it is possible to transfer values of cells this way?

I've attached the excel file for easier reference.

View 3 Replies View Related

If Statement - Visual Basic For An Input Form

Dec 10, 2009

I am creating a user input form where the user can enter data about what they have eaten on a certain day. I am using radio buttons, so when the radio button has been clicked, it automatically puts something into cell A1. What has been put into cell A1 will then be used to say what is in the combo box named Cbo_FoodItem. I have used the following
With Me.Cbo_FoodItem
If Worksheet("na").Range("A1") = "carbs" Then
Cbo_FoodItem.List = Worksheet("foodtable").Range("B2:B215").Value
End If
End With

However, when i try to test it, i get an error which then highlights the "Worksheet" after IF and says "Sub or Function not defined".
Obviously there is something wrong with the formulae, however i cannot work it out. Please help.
I've only just started using visual basic, but i am starting to get the gist of it.

View 9 Replies View Related

System Name And Time Update Based On Input?

Feb 26, 2013

In Excel sheet1 Column G has Names(John,Augutus,Jennie ect..) In Coulmn H i need to daily update the Present or Absent status, once i update the status i need Coulmn I to automatically fetch the System name and Column J should update with Time and date..

View 7 Replies View Related

Web Based Input Output

Feb 14, 2007

I got a question where I cannot think of a good descriptive title for, I have seen this done but do not know how it is done. An internet user inputs data onto a web form, and and presses a submit button. The submitted input is 'processed' on a through excel and outputs the result back the internet user.

Two questions
1 - what is a good title for this?
2 - how is it done?

View 3 Replies View Related

Offset The Input Or Output References

Jun 11, 2009

the actual output of the error message when I run my macro.
To repeat it, it says, "Regression - Having trouble offsetting input/output references"

The two button choices that appear on the bottom are "OK" or "Help." The OK button allows the macro to keep running (until it finds that it cannot find the regression output that was supposed to be created.

View 11 Replies View Related

Input/output Values Into/from An Array

Nov 6, 2009

I have a long list of values, a few thousand lines, that I need to input into an array so I can run a formula on the values in the array and then take those results and put them into another array which I can then call to output down a different column. Is there a faster way to accomplish this than the typical For Loop with the activecell.value and activecell.offset commands, that just takes forever.

View 15 Replies View Related

Multiple Output From Any Cell Input

Sep 18, 2006

Im working a spredsheet for my work, it is a database of qualifications for certain employees. There are about a hundred different qualifications and about 20 different employees. And some of the qualifications overlap. I would like to input a certain date in one of the cells, and have it copy into some of the other cells where the quals overlap. But i would like to input that date into any of the cells that overlap, not just one.

View 12 Replies View Related

Create System To Measure KPI?

Jan 3, 2014

I am looking to create a system to measure KPI (key performance indicators). I need to include a weekly target and then measure performance for that week against the target. I want to create a form to make data entry simple, how best it would be to implement using excel. Would I have 2 tables, one for targets and one for performance and then use look ups.

View 9 Replies View Related

Create A Basic Stock Quote Reader?

Jul 12, 2012

I am trying to create a basic stock quote reader.

I recorded what happens when I use the "from web" tool from the data tab. I then tried to modify it by allowing me to manually (with the inbox) put in the ticker that I want. When I run it, it does nothing. When I manually put in the ticker symbol for the company, it works.

For instance, I I were to replace

"URL;http://ca.finance.yahoo.com/q?s=" & TICKER & "&ql=1", Destination:=Range("$A$1"))
with
"URL;http://ca.finance.yahoo.com/q?s=ATVI&ql=1", Destination:=Range("$A$1"))

It would then work, but I would have to manually change it every time.

Here is the code
___________________________________________________________
Sub Macro1()
Dim TICKER As String
InputBox ("enter ticker symbol")
Cells.Clear
With ActiveSheet.QueryTables.Add(Connection:= _

[code]...

View 1 Replies View Related

Create Basic UserForm. TextBox, Buttons...

May 25, 2007

I want to do is have a User form that allows a user to add a new column across several sheets (i.e. b2, then c2, then d2) where what the user has selected from 3 defined ranges (in this case commodity, pack_type and pack_spec) is concatenated. see the example attached. So for exmaple, I would like the choice of adding into cell B2 across all sheets (except sheet pack) gold solid 5kg. Then I can add the next into c2. I have tried searching few a few examples of userforms with various problems but I dont know how to code it correctly.

View 4 Replies View Related

Link Input To Auto Output In Next Cell

Apr 9, 2014

Is it possible to have a code of say numbers or letter assigned to an explanation of that said code which automatically appears in the next cell?

See attached spreadsheet : Auto fill next cell with predetermined answer from list.xls

View 2 Replies View Related

Input Of Names Repeat As Shown In The Output

Dec 15, 2008

i have uploaded a file.....basically i have an input of names and i want to repeat these as shown in the output..is this possible.

View 5 Replies View Related

Clear Output Box When Input Dropdown Box's Are Changed

Jun 7, 2013

I got all three dropdowns to be dependent on each other, now however, whenever I change the value in the top Input Dependent Box M39, I would like M43 & M47 to appear blank. I want these two cells to appear blank until you select the dropdown which will allow you to select your values of choice.

View 2 Replies View Related

VBA Function: One Range As Input, Two Ranges As Output

May 10, 2006

I want to pass an array to a function, and am unclear how to do it. For example, the following declaration does not work:

Public Function xyz(a() as Variant) as Double

There is an additional requirement that I would like the output also to be a range of the same dimensions, typically one column and 100 rows. Is this possible?

A further twist is that the function I am writing calculates two values for every element in the input array and I need the output of them both.

An inelegant solution would be to write two separate functions, but I was wondering if it is possible to get two array outputs from a single function.

View 7 Replies View Related

Find User Input & Output Found Row

Apr 10, 2008

I am trying to write a match function to return the row of the value I search for. It is properly returning the value of the row I searched for. What I need to know is how do I get it to spit out information in the rest of the row. Here is my code so far:

Sub Rectangle2_Click()
Dim x As Long
Dim Row As Long

' Brings up input box
x = InputBox("Please Enter Tool Information", "Search")


'Exits if nothing is entered in the text box
'If x = "" Then Exit Sub
'MsgBox (SearchString)
'SearchString = x
MsgBox (x)

That gives me a Type mismatch error when I uncomment it out.

Something I was adviced of before in a situation like this way to Concatenate the columns, e.g
=A2&B2&C2&D2&E2&F2
in a far left column (insert 1 if needed).

That was supposed to be so that I can search through multiple columns. When I do that to test it, it never comes up with results. It always comes back as 0 meaning it wasn't found.

View 4 Replies View Related

Create A Macro For A Monitoring System

Jan 7, 2009

I have no experience with macros at all but i need to create a macro for work that will keep the original data entered but work out the difference between new data collected each day ...

View 10 Replies View Related

Create Room Reservation System

Jul 1, 2009

Please find it attached.

Copy all of the info from the main page to the guest lit (a new row each time).

From the guest list to the Gannt chart - i did a few tutorials on dynamic gantt charts using conditional formatting but cannot get them to work when based on data on a different worksheet.

Finaly is there a way to check for availabilty on any given date?

View 3 Replies View Related

Create Basic Leaderboard In Excel / Google Spreadsheet

Jan 3, 2014

I would simply like to know how to create a basic ranked leaderboard through EXCEL or Google Spreadsheet, which i will later on implement on a community forum. I would like 2 entries for the leaderboard column titles, being:

Column A: PLAYERS
Column B: POINTS

Then under A i will be typing all players manually, and similarly for B with points.

View 3 Replies View Related

Input Box To Create And Input Info Into A Formula Which Then Populates A Cell With A Value

Jul 24, 2014

I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:

Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)

Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:

=(NUM1*(NUM2*$H10))/$H10

So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.

View 9 Replies View Related

Logical Formula With Output Based On The Text Input

Dec 2, 2009

Sometime it’s so hard to explain what it is you are having problems with, specially in the little title. I'm trying to write a formula which should be very simple but isn’t . The formula is in box F10 if B10 has anything in it and its going to have a mixture of text and numbers (first text then numbers) then it’s should equal to the value in the box C7. Here is the formula I wrote that doesn’t work.

View 4 Replies View Related

Automate Calculation Of Output Based On User Input

Nov 2, 2009

What I am trying to do is get the ROW 13 and ROW 18 outputs depending on inputs in ROW 2 to 5. Inputs in ROW 2 to 5 can have any value in any year. I would like to perform the calculations if the value in column U is 2 (if 1 then no action required)

I don't need ROW 10, 11, 12 separately if that is easier ...I am interested in getting the ROW 13 basically.

View 6 Replies View Related

User Input Returns Selected Form Output

Dec 29, 2013

Have a person Input an Assembly Number and,Have Excel return a list of Part Numbers, their Descriptions, and a Quantity for each Part Number that will need to be packaged with the Assembly Number that was Input.

The output needs to be in the form of a check sheet (I can play with the formatting if I can get the information out) for the one Assembly Number input.The output can either be in a separate spreadsheet or a User Form. The spreadsheet is easier for me to work with but the User Form looks cooler.

I can use a VBA Function to Input the Assembly Number but then I need some way of searching a small table (about 20x20 cells) to find the matching Assembly Number and then return the Part Numbers, etc. to a check sheet so the parts can be packaged. Each Assembly Number can have several Part Numbers associated with it. Either unique to that Assembly Number or common to other Assembly Numbers. The Quantities can also be unique or common. A couple of simple examples might be:

Assembly Number - ANA0134
P/N Description Qty.
ANA0118 Widget thing 2
ANB0554 Thing Widget 4

Assembly Number - ANA0431
P/N Description Qty.
ANA0118 Widget thing 3
ANB0554 Thing Widget 4
ANC0023 Plain Widget 1

View 4 Replies View Related

Multiple Input Boxes & Output To Specific Cells

Jan 27, 2007

> When the workbook is opened I want a box to appear with a message and 4 choices (as buttons?).

> Based on the button clicked I want 1 of 4 new boxes to appear (replacing the first box) and ask for input values.

>I then want the inputs to be written to specific cells on a specified worksheets inside the workbook.

View 2 Replies View Related

Copy Input Data To Output Table Based On Variables

Mar 20, 2008

For what I'd like to do, the attached workbook actually represents 3 worksheet. one input sheet and two output sheets (output1 and output2)

I plan to use command button " CommandButton1" on input sheet to start the calculations

Here's what I want to do after pressing the " CommandButton1" :

1. start with case 1 and find the value of Input A to Input J from input table using Vlookup and paste those values to respective palceholder for inputs in blue colour area above the input table for each input.

2.for empty cell in the input table paste 0 (zero)

3. after this will give results for case 1 inputs on sheets "output1" and "output2" on basis of formulas used for each output

4.then copy the results for case 1 inputs from sheet "output1" and "output2" and paste them on "input sheet " in Output table (its below Input Table) next to case1

5.Do this for all cases (in actual workbook there will be more than 100 cases)

View 9 Replies View Related

Create KPI System In Excel To Measure Performance Against Target

Jan 3, 2014

Looking to create a system to measure KPI (key performance indicators). I need to include a weekly target and then measure performance for that week against the target. I want to create a form to make data entry simple, how best it would be to implement using excel. Would I have 2 tables, one for targets and one for performance and then use look ups, [URL]

View 3 Replies View Related

Index - Match Function To Output Data Based On User Input

Apr 30, 2009

I have a list of data that I'm using the index - match function to output data based on user input.

For example, I have:

1 10
2 15
3 20

So when '1' is selected another cell outputs '10'.

However what I want is for example if 1.25 is input, I want it to interpolate to spit out the correct number between 10 and 15.

View 5 Replies View Related

Excel 2007 :: Userforms - Input Cell Value (via Text Box) And Output Corresponding Values From Resulting Row

Jun 24, 2013

I have a simple spreadsheet of an inventory. Each row on this spreadsheet represents the data related to an item. I would like for a user to be able to INPUT a serial number via a userform. Then, I would like for the program to OUTPUT some information about that particular item (a few cells that should be on the same row as the serial number).

I attached an image diagram that may better represent what I am trying to do.

View 9 Replies View Related

Create Output List From Combobox

Jul 27, 2008

The following VBA code creates a list of selected listbox values when used in conjunction with a command button:

Private Sub CommandButton1_Click()

Dim lngLastRow As Long
Dim lngCol As Long
Dim lngIndex As Long

lngLastRow = Range("K" & Rows.Count).End(xlUp).Row + 1
lngCol = 11
For lngIndex = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(lngIndex) Then
Cells(lngLastRow, lngCol) = ListBox2.List(lngIndex)
lngCol = lngCol + 1
End If
Next

End Sub

This works fine for the listbox, however, for my application, I am using a combobox instead of a listbox. When I create a combobox and substitute ComboBox1 in for ListBox2 and run the code, I receive an error message.

View 9 Replies View Related







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