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 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
I can't quite seem to figure out the syntax for pulling a Range variable out of an array of type variant. I always seem to receive the error message "Object variable or With block not set", an example of my code as follows:
Dim currentRange As Range For i = 2 To UBound(myArray) currentRange = myArray(i) ' insert code here Next
I have also tried:
Dim currentRange As Range For i = 2 To UBound(myArray) Set currentRange = myArray(i) ' insert code here Next
Which results in the error "Object required". What is the correct syntax?
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 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 am trying to write a VBA procedure that uses nested For/Next loops to store the contents of a range in three worksheets to a 3 D array and then input the array into another location.
I have no problem doing this for a 2 D array, but am completely stumped on what changes I need to make in order to perform the same task on a 3 D array.
I will post my code for 2 D arrays below and will also post the uncompleted 3 D code, although, it is currently of no use because I am so lost on the 3 D array.
How do I reference sheet1,sheet 2 etc. when declaring my 3 D array? I know how to reference the rows and columns for my 2 D array by using the cells or range object, but what to do for the different sheets.
Two D Array:
Code: Public Sub For_Next_Two_D_Array() Dim I As Integer Dim J As Integer Dim MyArray(4, 4) As Integer
For I = 1 To 5 For J = 1 To 5 MyArray(I - 1, J - 1) = Cells(I, J).Value
Three D array:
Code: Public Sub Store_ThreeD_Array() Dim I As Integer Dim J As Integer Dim S As Integer
I've created a userform that uses parallel arrays to display strings that another macro pulls from a worksheet and the 2nd array holds strings of what the user types in. I've uploaded a workbook with the userform in it.
Part 1: I want to pass the 1st array (pSource) in during the initialization of the array and I want to send back the 2nd array (pUser) from the submit button, but I have no idea how to work it. I've tried a couple things from older posts in the forums, but had no luck with them.
Part 2: The arrays will be the same size, but the size isn't a constant. Is there any way to change the size of an array? I've been using 0 to 3 for testing the rest of the userform, but for the final product, I'll be passing an array in of a variable size.
Private pSource(3) As String 'Modify to work with variable size Private pUser(3) As String 'Modify to work with variable size
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.
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?
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
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 would like to use array v to store all the results of the for loop u...How can Ido it?
Dim myRange As Range Dim AnsRange1 As Integer Dim AnsRange As Range Set myRange = Application.InputBox(Prompt:="Select row to insert 10 rows below", Type:=8) AnsRange1 = myRange.Row Dim u As Integer Dim v As Integer Dim var() As Single v = 0 For u = 23 To 24022 Step 9 var(v) = u Next u If Not (AnsRange1 = v) Then MsgBox AnsRange1 Else Range(AnsRange1 & ":" & AnsRange1 + 9).Insert Shift:=xlDown End If End If
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...........................
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:
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 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 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".
I am trying to loop through column A and I want to store in an array where I find "App" within the cell value. I am trying to find "App" but will store the whole cell value in the array. I could not figure out the Find method, so I tried the MID function but am having no luck.
Here is my code:
Code: Sub Arraytest() Dim arr As Variant, lastrow As Long, i As Long, f As Long, l As Long f = 0 lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row ReDim arr(1 To 1, 1 To lastrow)
i have a very large spreadsheet filled with telephone numberS and some other codes that go with them...i need to match the codes with the phone numbers.
Sub TRCO() Dim TNs As Long Dim i As Long Dim TempArray() As String Dim TRCO As String Dim CD03 As String Dim ASOC As Range TRCO = "TRCO" CD03 = "CD034DF1" 'Filter TN's Columns("B:B").EntireColumn.Insert Set tempRange = Range("A6", Range("A65000").End(xlUp)) With tempRange . AdvancedFilter _...................
I would like to fullfil a range a cell with different strings. As the cells are jointed to each others, I would like to pass the string array to the range and not to pass each string to each cell, to spend time.
I mean, with the function
Dim stTxt(3) As String range(Cells(1,1),Cells(3,1)).value=stTxt()
But I have some difficulties with this function: -if the range is a column, the function works, but if the range is a row, then only the first value of the array is passed, and to all the cells of the range -I didn't manage to use this function when using
and when the range is in another worksheet. I need to use
(I'm using Excel 2000).
how I can pass correctly my string array to a row of cells? (cf 1. point).
i have two arrays that I want to use in a trend function. I don't think i can just use the array as is in the fucntion so my guess is that I need to pass the array into a range of data, and help on how I can do this? (also this is in VBA, fyi)