Function To Process String Input And Return Integer

Oct 2, 2006

i did some VBA quite a while back, and picking it up again.

I'm trying to get a function to read in a string, and return a value based on the string value.

Public Function value(ItemType As String) As Integer
If ItemType = " Upper" Then
value = 40
ElseIf ItemType = "Middle" Then
value = 50
ElseIf ItemType = "Lower" Then
value = 25
End If
End Function

When i tried using it at the Cell level, it keep giving error i.e. Invalid Name Error

View 9 Replies


If And - If Or Statements: Import ( On The Left) And Input By Hand To Get The Process Going

Dec 14, 2008

The questions are shown on the attached spreadsheet. There are 2 questions one on each sheet. On the spreadsheet the yellow cells indicate data that I'd import ( on the left) and input by hand to get the process going. The green cells have the correct numbers in them but I've had to manipulate them by hand.

View 5 Replies View Related

VBA Function To Search Partial String And Return Value?

Jan 28, 2014

I'm trying to come up with a VBA function that would return cell values based on another cell value.

I can do this by comparing a range (column of cells) with the cell value to match and then return the value of the cell next to it.

For instance,

The function should search a column for partial text, as follows:


TS 1.1

TS 1.2

TS 1.3

TS 2.1

TS 2.2

TS 3.1

TS 3.2

In the above table, the function should for partial text and return string as follows:

TS covered

TS 1.1, TS 1.2, TS 1.3
PDT 1, PDT 2, PDT 3

TS 2.1, TS 2.2
PDT 4, PDT 5

TS 3.1, TS 3.2
PDT 6, PDT 7

So basically I am searching for partial text TS 1. and so on

View 4 Replies View Related

Array As String And Integer Together

Sep 19, 2007

I have defined array say like this:
Dim myarray (1 to 5, 1 to 2)

Is possible to define something like this?
myarray (x, 1) as string
myarray (x, 2) as integer

x is anything between 1 to 5 .

View 9 Replies View Related

VBA Concatenate String & Integer

Sep 19, 2009

I am trying to concatenate some string and integer. It is always for previous month end date, i.e, 7/31/2009 or 8/31/2009.

Here is I have the data now
Profit and Loss for
After running the macro I would like to be
Profit and Loss for 8/31/2009
Here is what I wrote so far which does not work. I am sure there is simpler way to do it. I put the month end date 5 columns to the right for the time being and in the intention after deleting it after concatenation automatically.

For Each cell In Selection

ActiveCell.Offset(0, 5).Value = "=EOMONTH(TODAY(),-1)"
ActiveCell.Offset(0, 5).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"

View 9 Replies View Related

VB Function To Return CODE128b Barcode Font String ()

Feb 26, 2007

I found what looks to be a BASIC or perhaps early C form of code that will do exactly this, however I am finding it out of my grasp to 'convert' the below code over to VB for Excel, and I was hoping someone would be able to piece it together for me. Code128B is a barcode font that requires a 'computed' start and end bit, as well as a checksum character(s) in order for the barcode to be valid and readable by barcode scanners. This code (in whatever it is written, I don't really know), is said to do just that. Code:..................

View 4 Replies View Related

Finding A Specific Integer In A String

Aug 3, 2009

I have a string in one of the following formats:

Banana 2, Orange 5, Lemon 0
Banana 7, orange/Lemon 9, cucumber 6
Melon/grape 3, Pineapple/ Orange 1
Banana 1, orangefruit/Lemon 2, pine 8

I would like to take out the first integer that comes after the word Orange (not case sensitive). I'm kinda at a loss here, how do I go about accomplishing this?

View 9 Replies View Related

Test A String For Integer Or Real

Feb 22, 2010

I am not quite getting how to test a value if it is an integer or a real.

If A1 = 10.4 I want to skip that cell, if A1 is 10 I want to process that cell.

View 12 Replies View Related

Convert Text String To Integer

Feb 16, 2014

I've got an odd program that exports time data formatted as a text string. For example....


Since it has no numerical value, then I can't simply "format as number". I'm thinking that I will need to use one of the string methods eg: mid() to check each character individually and then convert that into the equivalent numerical value.

So the numerical output I am looking for in the above example would be 625.

View 2 Replies View Related

Combining String And Integer In Worksheets Name

Feb 19, 2014

for ii = 1 To 3
shts = "XX0" & Str(ii)
Create_worksheet (shts)
Next ii

when I run this, my worksheets are like
"XX0 1"
"XX0 2"
"XX0 3"

what's wrong that I did? How can I do "XX01"...?

View 3 Replies View Related

Formatting Cell To Show Integer Value When Used With A String?

Nov 29, 2013

I have a cell that gives a title and then a cell value:

="Design and Build "& if(B19>=0,B19,0)

The issue is, I need the B19 value it displays to be an integer. Right now it's giving me about 8 decimal places. I've tried formatting that cell and the cell it calls, but neither has any effect.

View 2 Replies View Related

Naming Range With Integer Or String Variable?

Mar 13, 2012

I am trying to name a Range in VBA with the following Case statement.

Select Case counter
Case "2": Set Wk(numWeeks) = Range("P1:Q136")
ActiveWorkbook.Names.Add Name:="Wk" & numWeeks, RefersTo:=Wk(numWeeks)

I have many more cases and have defined Wk(5) as a Range. What I am really after it is possible to to name the range using something similar to the code above that uses a predefined variable in the naming process.

View 4 Replies View Related

Return The Month Integer From A Date

Aug 5, 2008

Upon trying to use the Month function (to return the month integer from a date), in excell 2007, I get the

"Expected variable or procedure, not module

followed by (if I drill down into the help file)

There is no variable or procedure by this name in the current scope, but there is a module by this name"

error message

Do I assume there is a way around this (or if I have to do something to 'activate' the month function)

View 9 Replies View Related

Converting Variant Return Type From Evaluate To Integer

Oct 7, 2009

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?

View 8 Replies View Related

If Integer Function And Combine Value And Text

Oct 12, 2008

I'm trying to write a statement something like: B1 = data. B2=IF(B1/8=0,"",IF(B1/8=INTEGER,B1/8 & "8/8",B1/8+1)). This is meant to identify which relay on a relay board is associated with the given data. For example if data=3, B2 = 1 3/8 meaning the 3rd relay on board 1. If data = 77, B2 = 10 5/8 meaning the 5th relay on board 10. I could do without the 10, but the 5th relay on that board is important to me. In summary, I think my trouble is identifying when the product of B1/8 is an integer. I may also be having trouble combining value and text. Well, there it is. I may very well be going about it all wrong.

View 3 Replies View Related

Mode Function By Strings Instead Of Only By Integer

May 8, 2007

i've tried searching for "Mode Function in Text" , "Mode Function by Text" but to no avail. Can someone please guide me in how to do a Mode by Text? =MODE(number1,number2,...) But if i have a whole chunk of data in Strings in cells Range A1:C50

1) How can i get the mode of that Range ?
2) is it possible to get the statistic of those for e.g.

Apple 20
Pear 30
Watermelon 50

View 4 Replies View Related

If Text Found In String Return String

Jan 31, 2008

Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.

View 3 Replies View Related

VBA / Using String From Input Box As Criteria For CountIF

Aug 19, 2014

I'm trying to enter text (a staff members initials) into an Input Box so that the initials are then used as the citeria in a CountIF formula entered by VBA

A simplified version of the code I am using is:

Dim staffname As String
staffname = InputBox(Prompt:="You name please.", _
Title:="ENTER INITIALS", Default:="MC")
Range("H9").FormulaR1C1 = "=COUNTIF(RC[-3]:RC[-1],"" & staffname & "")"

The issue is getting the staffname string to be entered within the formula.

What is the correct combination of "" & to enable this to work?

View 6 Replies View Related

Pass Input-box String From Sub To Main

Oct 23, 2013

how to specify a file extension type for a browseforfolder part of what i've been doing.

It's not calling correctly as i'm not sure how to feed the inputData string back into the main.

Obviously there's probably better ways of doing this, but can my way be corrected easily?

I've been looking into ByVal and ByRef, but have only seen examples goin from main to the sub and not the other way.

Sub Main()
Dim strFolderName As String
Dim strFolderTest As String
Dim FRCntrHiLim As Variant
MsgBox "before calling inputbox"
Call InputBoxTest(inputData) '

View 4 Replies View Related

Including Column A String Into Input Box

Feb 24, 2009

I have a code of;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Me.Range("c4:f100")
Dim iSold As Long, iColumn As Integer
Dim strTitle As String
If Intersect(ActiveCell, Target) Is Nothing Then Exit Sub
iColumn = ActiveCell.Column
Select Case iColumn
Case 3

An what i need to happen id the input box saying "Please enter quantity of (whatever is in cell A11) assuming i am clicking on E11

so in other words, in cell A11 it says "MONITOR", i need the input box to pop up and say "Please enter quantity of MONITOR "

View 9 Replies View Related

Display Decimal Value Input As String Format

Aug 16, 2012

I'm writing a macro that will automatically change the display in a column of cells.

The input in the cell would be a decimal value, (e.g. 1, 1.25, 1.5, 1.75, 2). As of now, I think the only input options are whole numbers and 1/4, 1/2, 3/4 fractions.

After inputting the decimal value, the cell should update to display the value as the following string format :

Input: 1.25
Displays: 1-1/4"

The purpose being to enable fast data entry while displaying in the desired format.

Here is where I know to begin for the automatic update:


Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.EnableEvents = True
End Sub

View 6 Replies View Related

Auto Generation Of String Based On User Input

Jun 3, 2009

Without using VBA, how can i generate strings say


assuming user has inputed two numbers 1 and 5. I know you might have lots of questions like how the hell user input is coming when there is no usage of VBA.

View 3 Replies View Related

VBA For Inserting Input String Alphabetically Into Column Of Names?

Oct 2, 2013

I have a list of names in the A column of the sheet. I wish to use the input string, a new name, of a vba box to insert into the column of existing names in alphabetical order by inserting an entire row, which I suspect will be the case anyway. Also 'Return' key doesn't seem to be inserting a CR on the page that I typed this request upon.

View 5 Replies View Related

Function Returns Value To Cell As Single - Function Is Defined To Return Double

May 14, 2013

Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?

Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double


View 9 Replies View Related

How To Change Variable Data Type To String If User Input Is Not A Number

Jun 3, 2014

I have declared a 'long' variable. however; how do I change the variable data type to string if user input is not a number? how do I recognize the user input's datatype?

View 7 Replies View Related

Inputbox Validation Return To Input Box

Sep 22, 2009

Below, both inputbox cases appear to work fine. If you enter an incorrect input on your first attempt it will offer you the option to return you to an inputbox.

View 3 Replies View Related

Input Any Text To Return A Number

Jun 7, 2014

Here is my formula but I want to change the "wd" to just text (no matter what text is inputted"=IF(C5="wd",MAX($C$4:$C$23),C5)+Q5

View 2 Replies View Related

Input Box: Return 2 Values To 1 Cell

Jan 30, 2008

I am attempting to have a user enter a First Name into the first Input Box and a Last Name into the second Input Box.

Within the code, I would like both answers to be returned to cell A1.

For example: A1 would read as Doe, James or James Doe (any variation is fine, as long as its one value in A1).

Sub Name()
y = InputBox("Enter First Name", "Information")
If y = "" Then
MsgBox "You must enter", 16, "Message"
x = InputBox("Enter Last Name", "Information")
If x = "" Then
MsgBox "You must enter", 16, "Message"
Range("A1") = y & & x
End If
End If
End Sub

View 9 Replies View Related

How To Return Day Of Week When Input Date To A Cell

Apr 1, 2014

For example i input in cell A1 April 1, 2014 in cell A2 it must put Tuesday.

View 2 Replies View Related

Return Cell Data From List When Variable Input In Second Worksheet

Nov 25, 2013

I want to return the data from a list in a cell on worksheet1 if I input a variable on worksheet2. I need to avoid VBA and pivot tables.

Example, if I put "Expense" in the input cell on worksheet2, I want to return a list of each row that has "Expense" in it on worksheet1:

Worksheet 1 (ColumnA/ColumnB):


Worksheet 2 required output (no row gaps or spaces):

Input cell "Expense" - in A1

List required (A3:B5):


View 9 Replies View Related

Copyrights 2005-15, All rights reserved