Assign The Workbook Name

Sep 8, 2009

Sub FileProcessing()

Dim InputFile As String
Dim wb As Workbook

InputFile = ThisWorkbook.Sheets("Sheet1").Range("C3").Value

Workbooks.Open Filename:=InputFile

wb=InputFile

'more CODES here

End sub
I keep getting error on the line:



wb=InputFile
The value in ThisWorkbook.Sheets("Sheet1").Range("C3"). is

C:Documents and SettingsPATSYSDesktopInput0909.xls

View 9 Replies


ADVERTISEMENT

Assign Value From Closed Workbook Cell

Nov 11, 2006

Is it possible to write a macro in Book1 to assign Public Varibles in Book1 to values located in cells in Book2 without first opening Book2. So lets say in Book2, sheet1, cell B2 contains the number 5. I want Public Variable "Number_value" in Book1 to equal the number 5.

View 3 Replies View Related

Open Workbook And Assign To Variable

Jul 16, 2007

Back to coding in VBA after long months of Matlab, and banging my head around this simple code snippet:

Sub openfile(FilePath)
Dim myWBk As New Workbook
'Now open Form
myWbk=Workbooks.Open(FilePath)
End Sub

This returns a nasty error "Activex Component couldn't create object". It opens the file given by FilePath though. What I would like is to assign the open workbook to the myWbk variable, such that it is easier to work with.

View 2 Replies View Related

VBA - If Cell Contains Text Within Array In Different Workbook / Assign Value

Oct 20, 2012

I have a worksheet (WORK1) that contains two columns of values that I can add to or change if necessary. The first column would be a text string (STRING), the second column would be its category (CATEGORY).

Example:
STRING CATEGORY
Chevron Gas
Exxon Gas
Vons Groceries
McDonalds Hamburger

In a separate workbook (WORK2), I have a column (A) which contains text. Ex:

A1 = "Station 2134 Chevron Motor"
A2 = "ExxonOil 234"
A3 = "Vons store"
A4 = "Hamburger McDonalds"

I would like to assign B1, B2, and so on... a CATEGORY if it contains STRING.

For this example, B1, B2, and so on... would be assigned the values.

B1 = "Gas"
B2 = "Gas"
B3 = "Groceries"
B4 = "Hamburger"

View 5 Replies View Related

Assign Column With ID

Oct 23, 2008

i have column with 40.000 entries. I have added to the column entry my ID
=B1&"?aid1425". How can i get rid off colum A without effecting column B with the ID?

View 3 Replies View Related

Assign Formula To A Name?

Dec 22, 2012

Is it possible to assign a formula to a name that is defined and use the defined name, that will be easier to use.

say =VLOOKUP($A:$A,Sheet1!$A:$B,2,FALSE)

I would like to use the above function and name it. Is there a way out?

View 3 Replies View Related

Assign Value Given Another Value In Range

Jan 21, 2013

I am doing an assignment about credit rating and I am trying to make a spread sheet based on Moody's credit rating method.

I have more or less finished my model, but I have one final problem; in the end I will come up with a numeric score, which can be translated into the final rating (Aaa,Aa1 etc.) based on some ranges. I just can not figure out how to formulate the formula, especially because I have no experience working with ranges.

I have tried with some IF formula's, but they seem to be way too long and complex, for what I imagine should be a relatively simple task.

In the attached sheet I have a final value of 6.9, which should give an A3 rating given that it is within the 6.5 - 7.5 range.

Test sheet.xlsx‎

View 2 Replies View Related

Assign Number To Row ....

May 1, 2009

I have a list of numbers such as:

A01
A23
A53
A64
A74
A128
B01... the goes on too approximatly D128

and i wanted to know if there is a way to assign the value to the row?
For example:
A23 in column A row 23
A53 in column A row 53

is there any method that achieves this?

View 10 Replies View Related

Assign Variable For Next Available Row?

Jan 15, 2010

This code finds the second avaliable row from the top of the sheet.

View 3 Replies View Related

Assign A Value To Text

Sep 22, 2005

I have read a lot of posts, and maybe I am way out of my leage.

I am trying to setup a spreed sheet for our fire department.

We want to create a way to track training pay. When someone attends the
training we want to just put an "x" in next to their name in the column for
that training event rather then 9.75 for example. But we also want to be able
to add up all training for that individual for the year and see what we paid
them.

The idea is we have a sheet that shows us checks of who attended and who
did not. But we also want to add up the pay associated with that x.

So:
We want column A to be member names

Column B is a training event.

We want to go down and put in an "X" for each member who attended.

Now What I want is to have that "x" be associated with a dollar value. For
example lets say that they get $10 for that training.

THe idea is that I can run a total at the end of the year for each member
for each "x" and see how much money they recieved. I.E. the "x" is a hidden
value maybe that when I add up all the "x"s for a member it will give me
total dolar value for all training they attended.

View 9 Replies View Related

Assign Text To A Value?

May 29, 2012

I want to assign a text to a value. e.g if i have 2, it should come up as negative.

View 9 Replies View Related

Assign Value To A Column?

Jun 11, 2013

I want to assign a value to a cell so that when a word is entered into the cell, it registers with a sum formula at the end of that row. I am trying to rank a group of people based on organization membership. For example...

John Smith gets 30 points for being president of a team, 20 points for being vice pres, 10 for being secretary, etc. There are 10 teams, A-J, I want to be able to just enter the letter of the team into a cell in the column corresponding to the position that John Smith holds.

At the end of Johns Smith's row, i need a sum formula that gives me a numerical value to rank each person based on involvement.

View 1 Replies View Related

Can't Assign To Array

May 30, 2014

I don't see why I cannot populate the following array The usedvar is an array of letters and I'm trying to build a new array that converts them into ascii.

Code:

Dim usedvar_asc() As Variant, f As Integer, g As Integer, temp_usedvar_asc As Integer
Dim temp_usedvar As String
For f = 1 To UBound(usedvar)
temp_usedvar = usedvar(f)
usedvar_asc = Asc(temp_usedvar)
Next

View 9 Replies View Related

How To Assign The Whole Row To Another Sheet's Row

Apr 18, 2007

how to assign the value of whole row from one sheet to another sheet?
Worksheets("Result").Cells(count, 1).Value = Worksheets("Current").Cells(k, 1).Value
the above code assign only the specifc cell value. but i need to assign whole row

View 9 Replies View Related

Assign Computer ID

Feb 9, 2009

Does anyone have a macro that could assign a 16-digit number, seperated by dashes, to a computer? (i.e. Computer ID: xxxx-xxxx-xxxx-xxxx)

View 9 Replies View Related

Would Like To Assign Ranges For Each Row

Apr 5, 2009

I am defining ranges for cells in a row by the formula:
Set myrange3 = Range("B13", Cells(13, Columns.Count).End(xlToLeft))
Set myrange4 = Range("B14", Cells(14, Columns.Count).End(xlToLeft))
Set myrange5 = Range("B15", Cells(15, Columns.Count).End(xlToLeft))
Set myrange6 = Range("B16", Cells(16, Columns.Count).End(xlToLeft))
Set myrange7 = Range("B17", Cells(17, Columns.Count).End(xlToLeft))
.
.
.
.
.
Set myrange62 = Range("B72", Cells(72, Columns.Count).End(xlToLeft))

The last one is 62 assuming data wont go over 62nd line. However, there would be always a room for error if this is done manually. Is there any way, I could assign range variables only till that row which has data

For example
The data below shows I only need to define range variables namely myrange1 including cells 2 and 5 till myrange7 of cell values 9 and 6.

A B
2 5
3.4 8
5 12
6 23
77 56
8 7
9 6

The syntax:
LastRow = Selection.SpecialCells(xlCellTypeLastCell).Row helps by giving the number of the last valid data row, but I am not sure how this might be used if at all in automatically assigning ranges of valid row cells to variables.

View 9 Replies View Related

Assign Number To Text

Jan 23, 2014

I currently have a spreadsheet which I use to do an audit of the office stationery supplies.

There 3 columns at the focus of this question:

F - Number of items in stock
H - Stock re-ordering level
I - Order Required

I currently have an IF statement to tell me if I need to re-order any stock, by comparing the Number of items in stock against Stock Re-ordering level.

The statement is =IF(H1>=F1, "Yes","No")

This works fine under most circumstances. However, in some of the H cells, I have the text "Special" which indicates that the stock will only need re-ordering on a special occasion.

Any value I enter in F will cause the I to say "Yes".

Is there any way to make the word "Special" equivalent to 0, so I says "No"?

View 4 Replies View Related

Assign Value To Character Or String?

May 30, 2014

I want to assign a value to a character or string

Like I have formula p2p3= (n-1)*p +(2*e)

I want to assign values(number) to n,p,e.............so that i can get p2p3....like want to assign 'n'=4

I don't want to use cell number for calling values of n,p,e

View 1 Replies View Related

Assign A Name To A Range Of Cells

Oct 24, 2008

Is it possible to assign a name to a range of cells, so that when you insert a formula into another cell you can call that range of cells into the formula?

View 5 Replies View Related

Assign Text Value, Then Count

Nov 10, 2008

First challenge will be assigning point values to Words so I can them up and create averages etc

So I have 4 words I need to learn to assign point vales to, then create a formula that will count the points in a row. That will get me off and running,

So here is a very vague idea of what I mean, this would go in A5 as a example

if A1:D1= complete+2 incomplete+0 submitted+1 missing-1

so if I had a row that was:

Submitted | Submitted |Complete| Complete | Missing | 5

( 5 would be the value the formula would return 1+1+2+2-1)

View 4 Replies View Related

How To Assign Value To Each Name In Dropdown List

Mar 25, 2014

I have recently found out how to create a dropdown list (easy). Now I am wondering if it is possible to create a dropdown list that has a value assigned to each name in the list?

For example: I have the following list;

Australian states:
1) QLD
2) ACT
3) WA
4) NT
5) NSW
6) TAS
7) VIC

When a person chooses their "state" I want there to be a corresponding value attached to it. These values are:

1) QLD - 0.82
2) ACT - 0.87
3) WA - 0.78
4) NT - 0.69
5) NSW - 0.87
6) TAS - 0.20
7) VIC - 1.17

Is this possible?

View 4 Replies View Related

Assign Two Macros To Same Button

May 10, 2014

How can I execute two macros with one button press?

the sub names are CDO_Text and CDO_Mail

View 2 Replies View Related

How To Assign Number To Course Name In Input Box

Jun 26, 2014

In column 'o' I have inserted a number for course name in column 'p'. I want to assign a course name to that particular number as follows:

Column 'o' Number ... Column 'p' Course name
1: A. B. C.
2: J. K. L.
3: P. Q. R.
4: X. Y. Z.

And so on as per course names.

If I enter number 1 in inputbox then I want to enter the course name in column 'b4' respectively.

View 2 Replies View Related

How To Assign Two Actions To One Button

Jul 3, 2014

I need to assign two actions to a one button.

1. action: Copy Column A, paste to Column C
2. Write time&date into G5

I am only capable of doing one button for each action using the macro recorder and simple code:

[Code] .....

View 2 Replies View Related

If Cell Contains Word Then Assign Value

Jan 29, 2010

Basically I have columns A and columns B.

Column A contains a list of words
Column B is blank.

If a cell in column A contains a word like "univ", I want the number 1 to appear in the respective row in column 2.

If a cell in column A does not contain a word "univ", I want the number 0 to appear in the respective row in column 2.

View 14 Replies View Related

How Do I Assign A Code To A Button

Aug 26, 2007

I have had the following written for me


Private Sub Worksheet_Change(ByVal Target As Range)
For Each Cell In Target
If Cell.Column = 4 and Cell.row >= 17 and Cell.row <= 20 Then
If Cell <>"" Then
Cell.Offset(0, 3) = Date
Else
Cell.Offset(0, 3) = ""
End If
End If
Next Cell
End Sub

I wish to assign that code to a button on sheet 1.

I normally just right click the buitton and assign the macro, but on this occasion I can't. I think it's to do with it being a private sub.

View 14 Replies View Related

Assign ListBox By Code

Dec 8, 2008

I'm trying to assign a list box to a variable using Set statement, but getting an error.

View 8 Replies View Related

Assign Array To Variable

May 19, 2009

My question is about assigning an entire array to a single variable. In this case, I want to assign an entire array to one element of another array.

View 6 Replies View Related

Script To Assign Formula.

Jun 9, 2009

Need write a script that assigns a formula to a cell everytime the workbook is opened. Currently when I synce the workbook with SharePoint, it deletes the formula from the cell.

View 4 Replies View Related

Losing 'assign Macro' After Use

Aug 17, 2009

i have the following code for sending a worksheet to a closed workbook

View 14 Replies View Related







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