Need exact VBA code syntax to assign a workbook (to be closed) sheet's cells J4 to J72 values to a variable called "ColJValues" to be assigned to another sheet (to be opened later in the macro). The values are all dates. Once the other workbook is opened later in the macro, need the exact syntax to assign the value in the above variable, "ColJValues", to it's cells J4 to J72.
View 8 RepliesI maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
I'd like to assign a value located in a cell in a workbook to a variable in a VBA program in another workbook. I do NOT want to open the workbook that contains the cell with the value. I can't get the hang of referencing an external cell reference. Let's say there's a value in a cell named "nbr" in a workbook named "tst.xls" located in a path "c: est" that I want to assign to a variable "tstnbr" in a VBA program, what is the best way to code that?
View 8 Replies View Relatedhow to set a cells value into a variable, using .value, then set another cells value equal to that variable without using copy/paste
What I can't figure out is how to see the value of multiple cells to a variable and place them into another range of the same size using .value. It would be nice to free up the clipboard.
I need a macro that will create a sheet at the end of the workbook.
Sum data from a variable amount of sheets and display that data on the created sheet.
Here is a step by step:
Starting on sheet 5.
Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.
Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.
(Note: The data would also need started on row 4. Everything above row 4 is headers)
Here is a small example:
D E T U V
13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1
I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).
After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.
So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).
I'm attempting to assign 1 of 4 predetermined values to a cell based on the values of entries in another cell.
View the example attached : Value assignmts.xls
Assign the Value of 1, to (D8) when any value entered in (C9) is equal to or over 380, but less than 410.
Assign the Value of 2, to (D8) when any value entered in (C9) is equal to or over 410, but less than 440.
Assign the Value of 3, to (D8) when any value entered in (C9) is equal to or over 440, but less than 470.
Assign the Value of 0, to (D8) when any value entered in (C9) less than 380.
I am trying to make a macro that when clicked, will randomly choose one of 7 values and type that into cell C4. Values can be Dodgeball, Football, Soccer, Tetherball, Soccer, Baseball, Basketball. Is this doable?
View 9 Replies View RelatedIn my workbook sheet 11 has some ranges that need to have names based on cell values in sheet2 (for purposes of data validation lists).
Range S28:S46 will assume the name of sheet2A11 & sheet2A3.
(example name period_1unit_1)
Range U28:U46 will assume the name of sheet2A11 & sheet2A4.
Range W28:W46 will assume the name of sheet2A11 & sheet2A5
Right now I am calling the code when something is entered into A11.
I have tried if statement and select case, but I ran into complications with both.
I have posted both codes with the questions I have concerning those codes.
For example, I have the numerical value "400" in R1C2 and that value would be used in the name "D400" which is to be assigned to R1C1:R5C2.
The code I have is:
Range("R" & DRow & "C1:R" & DRow + 4 & "C2") = "D" & Cells(DRow, 2).Value
but is throwing up the error:
"Run-time error '1004':
Method 'Range' of object '_Global' failed
DRow increases dependant on other variable data.
Also, am I right to be using R1C1 instead of A1, or can the same be achieved using A1 references? If so, I think I may not be able to see the wood for the trees.
There are 10 rows of data, and would like to assign value at the end of each row as "initiated" "In the process" "Needs to be Reviewed" "Completed", by using Buttons with different macros.
View 2 Replies View RelatedIs it possible to assign values to letters (eg. a=1, b=2, c=3... z=26) and then count the total sum of those letters in a cell
Example
A1= car
car is c=3 + a=1 + r=18 which would make the total to be shown in A2 3+1+18=22
With this code i would like to create a subtotal of all letters per row and a batch total of the column with subtotal.
I have a range of data on one worksheet that is pulled into a pivot table on another worksheet. Looking at the table below I would like to assign the values in the pivot table to a 1 if there is data. For example if any field is >0 I would like to assign it a 1. If not leave it blank. I tried using the formula function in the options tab but didn't have any luck.
Car
Truck
SUV
Color
Black
1
15
Red
7
1
Blue
1
5
I have two tables below. First table contains the list of domain names (given just for example, actual list may contain several more such Domain names) with the group names assigned to them. Second table shows the criteria for assigning them the group names:
Sheet2ABCDEFG1Table 1Table 22Domain NameGroup NameDomain NameGroup NameDomain NameGroup Name3NCG-W3K-SAT14-0DOSSNCG-W3K-EEC-SUN16-3EECEXCEEC4SVL-WIN-SAT08-0LABSNCG-W3K-SUN16-0DOSSDCDC5SVL-WIN-SAT06-0LABSNCG-W3K-TUE01-0DOSSNCGDOSS6NCG-W3K-MON04-0DOSSNCG-WIN-EXC-SUN21-0EECDMZETIS7NCG-W3K-EXC-MON04-3EECNCG-WIN-EXC-SAT16-0EECSVLLABS8NCG-W3K-EXC-MON03-0EECNCG-W3K-X64-DC-SAT14-0DC9NCG-W3K-EXC-TUE01-0EECDMZ-NCG-W3K-X64-SUN12-0ETIS10NCG-W3K-EXC-SUN15-0EECDMZ-NCG-W2K-SUN12-0ETIS11NCG-W3K-X64-EXC-SUN16-0EECNCG-W2K-SUN20-0DOSS12DMZ-NCG-W3K-SUN11-0ETISNCG-W3K-MON03-3DOSS13NCG-W2K-DC-SUN11-3DCSVL-DMZ-WIN-SUN12-3LABS14NCG-W3K-X64-DC-SUN10-3DCSVL-DMZ-WIN-SAT10-0LABS15SVL-DMZ-W2K-SUN12-3LABSDMZ-NCG-W3K-SUN12-0ETIS16NCG-W3K-EXC2-MON03-3EECDMZ-NCG-W3K-SUN10-0ETIS17NCG-W2K-SAT17-0DOSSNCG-W8K-X64-DC-SUN10-4DC18NCG-W3K-X64-SUN10-0DOSSNCG-W3K-TUE01-3DOSS19NCG-W3K-TUE00-0DOSSNCG-W3K-DC-TUE02-0DC20NCG-W3K-X64-SAT15-0DOSSSVL-WIN-SAT11-0LABS21NCG-W3K-WED00-0DOSSSVL-WIN-SAT07-0LABS22NCG-W8K-X64-SUN19-0DOSSNCG-W2K-SUN17-0DOSS23NCG-W3K-EXC-SUN20-0EECNCG-W3K-SUN20-0DOSS24NCG-W3K-ULZ-SUN14-0DOSSNCG-W3K-TUE22-0DOSS25NCG-W3K-TUE03-0DOSSSVL-WIN-SAT07-3LABS26NCG-W3K-ULZ-TUE00-0DOSSNCG-W3K-MON05-3DOSS27NCG-W3K-SUN19-0DOSSNCG-W8K-SUN18-3DOSS28NCG-W3K-EEC-SUN19-0EECNCG-W8K-SUN22-0DOSS29NCG-W3K-EEC-SUN20-3EECNCG-W8K-X64-DC-SUN11-0DC30NCG-W3K-EEC-THU00-0EECNCG-W3K-SUN17-0DOSSExcel 2007
My concern is to automate this process using a VBA code. How can I search for the keyword given in Table 2 in the first column of Table 1 and assign the group names as it is assigned currently, considering the below conditions and criteria.
1) The correct group name should be assigned to the Domain name containing the keyword given as per criteria in Table 2
2) It is possible that the domain name will include more than one keywords given in Table 2. For example Cell number A7 contains both keywords NCG and EXC, in such cases the preferance should be given to EXC and we will assign the group name as EEC. And in Cell number A12 also contains both NCG and DMZ, again the preferance should be given to DMZ and we have to assign group name as ETIS.
That means we will give second preference to keyword NCG. If domain name contains none of the keywords except NCG as per given criteria then only we will assign grup name DOSS.
This code finds the second avaliable row from the top of the sheet.
View 3 Replies View RelatedMy 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 Why the variable DataRange won't set as below. I get Run-time error 9: subscript out of range.
Code:
Sub CreatePivotTable()
Dim DataHeaderRow As Integer
Dim DataRowCount As Integer
[Code].....
how to do is the assignment of GoodArray1 to CurrentArrayToUse, i.e. "CurrentArrayToUse = GoodArray1" below:
Global NextArrayToUse()
Global CurrentArrayToUse()
Global PreviousArrayToErase()
Global GoodArray1(), GoodArray2(), [etc]
Global CurrentGuessNumber As Integer
[bunch of code, part of which assigns a number to CurrentGuessNumber, then the following...]
Select Case CurrentGuessNumber
Case 1
CurrentArrayToUse = GoodArray1
NextArrayToUse = GoodArray2
Case 2
CurrentArrayToUse = GoodArray2
NextArrayToUse = GoodArray3
PreviousArrayToErase = GoodArray1
ReDim PreviousArrayToErase(0, 0)
I am trying to assign a range of cells to a variable but it isn't working. My code is as follows:
Dim VarRateTable As Range
Private Sub CalcBudgets(rng As String)
' Dim VarRateTable As Range
Dim CurrentRng As Range
Set CurrentRng = Selection
Set VarRateTable = Range(rng)
Private Sub NEWCALC(datarow)
Dim vt As Double
vt = Application.WorksheetFunction.VLookup(Cells(datarow, 4).Value, VarRateTable, 3, 0)
but the Set statement gives me an error: <method 'range'of object '_global' failed>
I have created a userform with 3 textboxes and I want to return the values entered to variables for use in a procedure. I cannot figure out how to return the values.
Behind the command button I am unloading the form so that the calling procedure continues executing but my vars cannot see the controls.
I have tried making them public outside of the calling sub, but nothing. Can anyone point out to me what I'm doing wrong?
Split from Copy, Cut, Fill Relative Or Absolute Formulas
Originally Posted by AAE
Welcome to Ozgrid.
Assume the following:
Cell A1: is the input cell
Cell B1: contains a number
Cell C1: formula = A1*B1 or $A$1*$B$1
Your formulas must contains absolute cell references, other wise Excel will attempt to update them to the new cell references when copying/pasting.
If you "cut", rather than copy, Excel will maintain the original cell references in the formula.
Both of these questions are well within the experience level of a user with above average skill level as your profile shows. Please lower it (before one of the Admins does it for you) to a more realistic level.
See this training page on how to toggle between relative/absolute cell references: [url]
Well cutting is a nifty trick, thank you for that, I don't believe you quite understood my first question.
Lets use my calculator for instance. Using something it has designated K (for no apparent reason) I can enter a formula, such as K=*2-4, and after that is entered every number I then type and press equals to it will run that formula and spit out an answer. (after entering that function, if I press "5" "equals" it will give me "6") Now what I'm wondering is if excel has a "K" function that I can assign to certain cells, namely a column, so that whenever a figure is typed in any of the cells in that column, the required operation is done, namely *.9685.
What I need is a macro that assigns manually selected cells/ranges (which may or may not be continious) to a Variables.
View 2 Replies View RelatedBack 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.
Putting the final touches on a project I have and the last element doesn't work 100%. the scenario: I have a function that I wrote based on the user's input of the desired column to perform the operation. As a method of error-trapping and ease of use, I set instructions for the code to activate:
1) if the user highlights multiple cells of the desired column
2) enters the letter of the column (up to "z", not case sensitive)
3) enters the number of the column
Sub Button5_Click()
Dim ColumnUsed As Variant
' find number of columns selected
For MyColNum = 1 To Selection.Columns.Count
Cols = Cols + 1
Next MyColNum
'find number of rows selected
For MyRowNum = 1 To Selection.Rows.Count
Rows = Rows + 1
Next MyRowNum..............................
I need a bit of help with the below macro which I am trying to create. I recorded the below vlookup, which works perfectly. It checks a list on sheet “Map” and returns a value depending on whether the reference is one of the 6 or not. These 6 are likely to change over time so I would prefer to declare them as variables rather than build them directly into the macro
View 4 Replies View RelatedI'm trying to find the sum of a range of values based on multiple criteria, and the criteria is that the fields all have to be identical, then sum them. I've attached a brief example spreadsheet that has the fields
A=City
B=State
C=Values
What I want the formula to do is first find the range of all the matching states, then find the range of all the matching Cities within the states, and then sum the values based on them having matching city values.
I've been able to do that with one criteria using SumIf, I'm not sure if this will help paint an image of what I want to do:
=ROUND(SUMIF($B$3:$B$11,$B$3:$B$11,$C$3:$C$11),0)
But I can't figure out the way to do multiple criteria against itself. Most of the results I get from Google using multiple criteria are using a set few values, and I can't seem to figure out how to alter those methods to work with my situation.
I need to assign to a variable, the total number of text entries in col. A (or alternatively in a named range).
View 5 Replies View RelatedI have set up a number of arrays e.g. DataSetA (1 to 100), DataSetB (1 to 100) etc.
Is it possible to use a string variable (e.g. DataIndentifier) to identify and assign values to certain arrays.
For example, under certain conditions
DataIdentifier would be set to "DataSetA" and under other conditions
DataIdentifier would be set to "DataSetB".
Having done this, I want to assign values such as DataIdentifier(1) = 3.4 or DataIdentifier (10) = 6.2 etc.
This obviously won't work because DataIdentifier is defined as a string not an array. How do I enable the string DataIdentifier to be used as an identifier for the array I want to assign values to? Therefore if DataIdentifier is set to "DataSetA" then effectively I want something like "DataIdentifier(1) = 3.4" to assign 3.4 to the array DataSetA(1).
I am doing a Regular Expression search on a string variable assigned to the HTML content of a Wikipedia search. However I am currently manually going to Wikipedia, searching for the term, saving the html page, opening the saved page with Notepad and then copying the content into a cell.
Can the above process be automated with VBA, how to assign the html content of a Wikipedia search to a string variable.
I run a large macro which combines and compares an Excel file with a download out of our SAP system. One of the selection criteria I have to put in is called 'Plant' and it is a single value which I select from a drop down list. (Values are 1010 1020 1030 1040). In the Macro the value is defined as:
Plant = Range("I21").Value
where I21 is the cell in which the Plant is selected. My question: How can I make the macro take two values into account at the same time, so 1010 AND 1020 in one go? I tried changing the list used in the drop down list but that does not work. Or is it impossible as the plant has been restricted to only one value?
I have a range staring with cell "A5" and going to Column "K8+" that I need to copy. My issue is that the number of rows to copy will change. I have the number stored a variable "a" in my coding. how do I code this to work?
View 5 Replies View Related