way to store a cell's location to variables.
Something like:
int a, b
Cell(a, b) = ActiveCell
I'm currently working with a fairly large worksheet, and I'm using Cells. Find to look for a specific cell. Then I want to Filter that column, but I can't figure out what column Selection.AutoFilter Field:=? should be.
I would like to record the address of the last set of cells that data was input into to a variable so that a user can choose to delete the last entry. An 'Undo' button really.
What I have is a user form that writes different materials to thier respective sheets in the database. (Material1, Material2, etc.) Some materials have a different number of variables (some have a width and some don't, but all have a quantity.)
This is the code I have for adding the material to the database (each material has it's own button with material specific code.)
Private Sub AddToMaterial1_Click()
Set c = Worksheets("Material1").Range("a65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = False
c.Value = Me.Material1Quantity.Value
c.Offset(0, 1).Value = Me.Material1Description.Value
c.Offset(0, 2).Value = Me.Material1Length.Value
Dim lastenty1
lastentry1 = c.Address
Dim lastentry2
lastentry2 = c.Offset(0, 1).Address
Dim lastentry3
lastentry3 = c.Offset(0, 2).Address
Dim lastentry4
lastentry4 = c.Offset(0, 3).Address
Dim lastentry5
lastentry5 = vbNullString
Dim lastentry6
lastentry6 = vbNullString
Application.ScreenUpdating = True
End Sub
The following code is what I am trying to do for a single button to clear the last entry to the database.
Private Sub RemoveLastEntry_Click()
Range(lastentry1).ClearContents
Range(lastentry2).ClearContents
Range(lastentry3).ClearContents '(There is always at least 3 cells to clear)
If lastentry4 = nullstring Then Exit Sub
Range(lastentry4).ClearContents
If lastentry5 = nullstring Then Exit Sub
Range(lastentry5).ClearContents
If lastentry6 = nullstring Then Exit Sub
Range(lastentry6).ClearContents
End Sub
I am having trouble using the find function. I need to store a date as a variable and then find this date on another worksheet. The date is in the following format:
dd-mmm-yy
This is what I currently have which gives me a run time error 91:
Code:
Dim DateSearch As Date
DateSearch = Range("C3").Value
Cells.Find(What:=DateSearch, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
I have a table in which I have a "patient" column and a "page" column. The macro searches for a patient's number, then looks if this patient has the page "900.--". A patient may have more than one page, which will result in many rows with the same patient number. So far, my macro uses the search function to find a patient number, then compares the cell next to the active cell to see if it contains the page "900.--". If not, then my macro searches for the next patient and so on until the page is found and noted into another workbook or none is found.
In order to stop the loop, I am trying to store the address of the first cell found into a variable "rFirstCell" so that it can later on be compared to another variable, "rSecondCell", which represent the active cell. When both are the same, it means all the available search results have been tested and the loop should stop.
Sub testing()
Dim rRng As Range, rFirstCell As Range, rSecondCell As Range
Set rRng = Worksheets("Overview").[a1]
Dim sDeath As String
sDeath = "death"
ActiveSheet.AutoFilterMode = False
If LCase(rRng(2, 15).Value) = "x" Then
If LCase(rRng(2, 9).Value) = sDeath Then
Workbooks("DM Endpoint pages_test.xls").Activate
Range("A1").Select...........................
How to store a value in variable after concatenation of two values and putting it into the same cell.
Let assume, in cell A1, we have value 1 (numeric). And in code i have a variable with stored value as "%".
Now i want to concatenate 1 and % and put it back into cell A1 as 1%.
I have a written a code, but seems to be wrong one.
Sub Percentage()
Per = "%"
lr = Sheets("Process Overview").Cells(Rows.Count, 3).End(xlUp).Row
For i = 10 To Sheets("Process Overview").Cells(Rows.Count, 3).End(xlUp).Row
If Cells(i, 4).Value = "p" Then
[Code] ........
I have a spreadsheet with near 300 tabs, each with a picture in the tab. The main tab has a list of all other tabs, the goal is to allow the user to click on a cell next to an entry, and have Excel flash the referenced tab to allow the user to see what the entry is referencing. I have written a simple macro that activates a desired tab, unhides it, displays a message box to pause the macro, rehides the tab, then returns the user to the main tab.
Rather than creating a macro for all 300 tabs and creating buttons I would love to use the Private Sub Worksheet_SelectionChange(ByBal Target As Range) or some variation thereof, to make my life much easier. The name of the tab is in cell A2, so I would want to have the user click on cell A1, activate the macro, then take A1 to A2 with something like A1 = A(x+1)->A2, then display the tab listed in A2. So rather than have 300 macros with Sheets("XYZ").Visible = True, I would love it to read Sheets(contents of referenced cell).Visible = True. with the contents of referenced cell coming from some manipulation of the cell I clicked on...
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow.
simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
Excel 2003 > Attached is a small model of what I am trying to accomplish. Cells B2 and B4 contain the same formula … a formula that calls a simple function. The function has a variable passed to it … and the value of that variable changes depending on the location of the cell. Now, see the function in Module1 … it is called CellCalc. If the variable passed = Jim then value = 3. If the variable passed = Jack then value = 8. That is straightforward.
Note also that when Sheet1 is activated, I calculate the cells from left to right and top to bottom … that is important. Here is the challenge. If B2 > 1 then I want to add B2 to B4 and set B2 to 1. I can set B4 properly but I cannot reset B2. You can see my 2 attempts that are commented out. Is there some way of accomplishing this … or am I simply stuck in a circular reference?
The alternative to this is to write a function that operates externally on these cells. That will work for sure but then the values of the calculations will overwrite the functions in those cells, thereby taking away the dynamic nature of this application.
I am creating a macro to automate data analysis for work, but I've become stuck.
I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.
I have some code (listed below) that will open 2 groups of files(for testing purposes, I have been using for only 2 file prefixes, but will need for upwards of 10, and more may be added in the future), depending on what files have been opened in the past (it will skip those) and then import the new ones.
I am now trying to clean up my code, and having alot of it abled to be maintained be editing a spreadsheet (administator controlled)
What I would like to do is something similar to:
defvar= cells(1,1) 'where cells(1,1) has all of the info for that file to import
Selection.TextToColumns defvar
I realize this will probably be a little more complicated than this, and may even be its own sub or funtion.
Here is my starting code, and it works fine: ...
I have data in E6-E67 on Sheet 1. Based on the date in A2 on that sheet, I need to paste to a column in Sheet 2. In excel, I am able to get the cell location through vlookup and get the correct column number/cell reference. When it gets to the paste location, I am stumped on how to format that line of code? Do I need sometime of variable? I tried to use the address/lookup code but it does not work.
I have excel 2013.
I have several words in sheet2.one word per line. every line in sheet1 should be checked and deleted if the line (colum 3 and 4) contains any of the words in sheet2. i decided to go with two for-loops, my Problem is a error in line 7 and 9 (indicated by arrows). it seems to me that ...Cells(...).Value is not allowed for strings. i already tried .Text, checked several VBA Forums but could not find a solution.
View 13 Replies View RelatedI 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 RelatedIs it possible to store format of a sheet in a variable?
I have one sheet. i want to select all cell. then copy the formatting of this sheet(template) in a variable. is that possible? does variable need to be a clipboard? if it is in clipboard how to reference it? i.e if i have two or three formats stored in a clipboard how to select and paste the second one?
once i store this in a variable, i will paste in new sheets(about 50 of them) so that all these new sheets have same format as the template.
what is the easiest to do that?
If I have a file open called test file, How I can find the file path of the file that is open and how can I store that's as a variable?
What I am doing is trying to open a file that excel closes in a save as process!
I have a workbook that is composed of forty (or so) worksheets containing data and a single summary worksheet that has command buttons that take the user to the appropriate data worksheet for their specific project. Each data worksheet is exactly the same in terms of where the header row starts, and the specific headings.
On each data worksheet there is a command button that when clicked, builds a pivot table of the data for the current project. I have been able to create VBA code that hides the columns containing the data and then creates the pivot table in the empty (unhidden) columns n the same worksheet. This works fine, but is not a good solution from a useability standpoint.
What I have been trying to do is when the command button is clicked I want to capture the name of the current worksheet as a variable in VBA, go to a separate worksheet to build the pivot table, and when the user clicks a ‘Review Data’ command button on the pivot table worksheet they are taken back to their original worksheet containing their data. Is this possible?
I have a list of names from cell A1:A10 in sheet "Input." Each of these names has its own corresponding sheet in the workbook. I want to be able to run the same exact VBA code for each sheet. In other words, I am trying to get my name variable to automatically change to the next value on sheet "Input." I'm sure this is pretty simple to do, but I can't seem to find anything that works!
View 5 Replies View RelatedI am looking for macro that when run, will open a file explorer window and prompt the user to select a folder and file where they have data stored. Then I need it to be stored as a variable and used as a part of a "Workbook.Open Filename" command.
The reason for this is that, I have a huge formatting marco stored within a workbook. When a user extracts a report from SAP, I want the workbook to grab the file that is extracted, open it and import all of the data in order to be formatted.
I need to store a known index, unknown value in a variable so I can increase the value and use it in a database. It is used as a version number for a part and the versions go like: "00" -> "AA" -> "AB" -> .... -> "AZ" -> "BA"... etc.
VB:
If tool.Worksheets("TRB Database").Cells(A, "R").Value <>
ThisWorkbook.Worksheets("Design Calculator, Q").Cells(7, "C").Value Or
tool.Worksheets("TRB Database").Cells(A, "AA").Value <>
ThisWorkbook.Worksheets("Design Calculator, Q").Cells(5, "K").Value Or tool.Worksheets("TRB Database")
[Code] ....
That is a part of the increase and when I try to increase "AA" by 1 it goes to "B" and not "AB".
VB: tool.Worksheets("TRB Database").Cells(row1, "D").Value = Chr(Asc(tool.Worksheets("TRB Database").Cells(A, "D").Value) + 1)
This is where I increase the value.
I had a quick question for something that should be simple but I can't figure it out. I have a column of sorted values, it goes
A
A
A
A
A
B
B
B
B
B
and I want to insert a row to separate the A's from the B's. But there will be a random number of A's. I can figure out how to count where the A's end. But I can't figure out how to insert a row. Any suggestions? I tried the two below but they don't seem to work. The for section is counting the A's, and it works, it's what comes next that's giving me a headache!
I have an issue with the below code:
VB:
LastRow1 = ActiveSheet.UsedRange.Rows.count
ActiveSheet.Range("R1:R" & LastRow1).FormulaR1C1 = _
"=IF(RC[-1]<>"""",HLOOKUP(RC[-1],Sheet2!R60:R61,2,FALSE),"""")"
The issue I have is that in sheet2 the HLOOKUP range i want to lookup will not always be in R60:R61 these rows vary depending on the amount of data above this range... is there a way of incorporating some sort of
VB : LastRow = ActiveSheet.Range("B" & Rows.count).End(xlUp).row + 1
To locate the bottom of the data and the start of the HLOOKUP range?? if is there some way of re-coding it?
I would like to create new empty folders from the list in column B.
The number of folders will vary depending on how many entry in column B.
I need the folders to be created in an existing folder in the "current directory" called "Shop_Drawings".
The following code may be able to be modified.
Sub CreateFolders()
Dim MyFile As String
Dim sDir As String
Dim rng As Range
Set rng = Sheets("Matdata").Range("B2")
While rng.Value <> ""
MyFile = rng.Text
sDir = "CurDirShop_Drawings" & MyFile
''above is where I am having trouble...don't know the correct syntax''
MkDir sDir
Set rng = rng.Offset(1)
Wend
End Sub
I have attached a sample workbook.
I have been getting by with code which requires changing the destination in the module whenever making folders in different diectories or drives.
Look at the tab "testing-allproduct" cell C2. If that value exists in the tab name "store-allproduct", cell c2, then take the value from "store-allproduct" cell a2, and put that value into "testing-allproduct cell" A2.
View 2 Replies View RelatedI'm sure this is a very easy question, but I have been having trouble finding the answer on this and other forums.
Our macro has a search routine where it looks through a column for particular cell contents. Once those contents are found, we want it to save that cell's location, go do something else, and then return to that location afterwards. How would we go about doing this?
What I am trying to do is during a loop operation, which subtotals variable ranges, I want to store the locations of the cells that it puts the sum function into. I.E. if based on criteria it determines that range E4:E12 is summed into E13, I need to save E13 to use in a formula once I'm out of the loop. I don't know how many instances it will find and there's the possibility in the future that not only will the number of instances increase, but the location could always be different as well. Any way to do this without a million lines of code.
View 7 Replies View Related1. Store the value in Cell A1 to a text file located in C: with name TEXTFILE.TXT (Replace the existing value)
2. Store the value to the text file as additional line item (append records)
I am trying to use the Indirect function to use the value in a cell to select a sheet with the same name as the value of said cell. I have looked at several posts and attempted multiple methods. I still get an error. I cannot find a thread describing exactly what I am trying to do, which is:
reference a cell value to direct the formula to the correct sheet to then complete an array Sumproduct formula.
Since this description barely makes sense to me reading it, I have attached an example. The problem is in cell C7. Basically, I want cell c7 to look at cell c1 and then go to the tab with the matching name as cell c1 and complete the calcuation.
I do data entry for a webstore and one cell is used to map out the exact product category/subcategory path. I currently have to copy the appropriate path from a long list on one page and then paste it into the cell. I have to do this a hundred times a day. It would be nice if each cell within that column can have these values stored in them so I can just click on the cell and open up a drag down box and select the needed value. Is this possible to do in Excel?
View 2 Replies View RelatedI have created a number of complex functions that use or create 1-dimensional numeric arrays in VBA, and I would like to store and retrieve some of these arrays within single cells of a worksheet so that I can use them in dependent functions without having to store and display the entire array with one element per cell, and without having to recalculate the same intermediate array multiple times within VBA.
Are there any existing worksheet or VBA functions (or is it possible to create two functions) that can convert and store an entire numeric array within a single cell of a worksheet (e.g., as text), and then convert this back into a form which can be read and recognized as a numeric array by another function?
Or is there any other way to avoid filling my worksheet with arrays, or having to recalculate them each time within VBA?
I have in column B, cells that have any number, one number or several like B2.
#
A
B
C
D
E
[Code]...
Thinking that I have a loop for rows 1 to 3:
When in column "A" is "No", I want to consider values in C and D to get:
Code:
a[1]=Cells(1,"C") & "-" & Cells(1,"D") & "-" & "ABC"
But when in column "A" is "Yes", I want to store in array each value within cell in B (in this example B2) to apply later a For/For Each
over each number (in this example are 3 values only within cell B2), something like:
Code:
a[1]=Value_In_B2[1] & "-" & "ABC"
a[2]=Value_In_B2[2] & "-" & "ABC"
a[3]=Value_In_B2[3] & "-" & "ABC"