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.
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
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
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 have created a work diary in Excel which is built up in half hour blocks. I am able to create my appointments (usually one or two hour blocks) by selecting a group of cells and running a macro which merges the cells, colours them and puts a border round them.
I now want to be able to delete individual appointments by selecting the appropriate appointment and running a macro to copy and paste from the same range of cells in another worksheet (which is effectively my blank master diary page). I have tried work out what code I need to put the range of the current selection into a variable in VBA but I'm not having any luck.
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.
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.
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
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...
how to store the variables selected in a list box for future use in the macro?
I've got code as follows:
This is in my main module
'These set up the global variables Public SelectedRegion As Variant 'I've also used string Public NewTabName As Variant 'I've also used string
'This links the list box to the values in a hidden tab named "Regions" and shows the form WSForm.RgList.RowSource = "Regions!A1:A10" WSForm.Show This is in my coding for the form itself (list box & buttons)
'Code for my "Cancel" button Private Sub cmdCancel_Click() Unload Me End Sub
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.
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.
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
Is 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.
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?
How can you access a cell's value via a vb variable that contains the cell's address. I have a vb variable named cellAddress (string) that contains "$A$1" and I want to assign the contents of what cellAddress points to another VB variable - how do i Do this?
e.g. if cellAddress = "$a$1" and A1 contains "xyz", I would like to assigne "xyz" to a new vaiable by referencing just cellAddress??? Also - Can anyone reccomend a good Excel VB book? A book that maybe stresses the VB language rather then a cook-book approach.
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!
I 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")
That is a part of the increase and when I try to increase "AA" by 1 it goes to "B" and not "AB".
Code: Dim MyArray as Variant Dim Address MyArray = Range([a1], [b10]) For varRow = 1 To UBound(MyArray, 1) ' I want to know the current address of the cell right here, ' For instance I want to know I'm working with A1 right here ' Is that possible?
Next I guess my question is how do I find out the originating address of the cell I'm working with in an array. I need to check the font color of A1,B1,C1 etc while working in that range and I have no idea how to access it.
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.
Assume the following list of addresses are all in separate cells of a single column (A1-A4). I just need the formula to extract the street addresses, and then a separate formula to extract the zip codes.
5430-44 PASCHALL AVENUE PHILADELPHIA, PA 19143 OPA/BRT#: 884350845
4010 MARPLE STREET PHILADELPHIA, PA 19136 OPA/BRT#: 651087200
2618 SOUTH HOWARD STREET PHILADELPHIA, PA 19148 OPA/BRT#: 391251216
5737 WOODCREST AVENUE PHILADELPHIA, PA 19131 OPA/BRT#: 522155600
I'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?