Store Date Variable And Use To Find Cell
Jan 14, 2014
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
View 4 Replies
ADVERTISEMENT
Jun 4, 2013
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.
View 2 Replies
View Related
Jul 13, 2006
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.
View 6 Replies
View Related
Sep 12, 2006
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
View 9 Replies
View Related
May 1, 2006
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...........................
View 4 Replies
View Related
Dec 16, 2011
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] ........
View 3 Replies
View Related
Apr 20, 2013
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...
View 4 Replies
View Related
Sep 20, 2007
How do I use VBA to send the value of a cell, for example, cell J77 to the variable "mastervalue1"?
I have a spreadsheet that has a column with dates ( in the format "mmm-yy") and next to it a column that has values. I want to pass the value of the cell that is next to the date cell that contains today's month to a variable in VBA. Auto Merged Post;here's an example of my spreadsheet
View 9 Replies
View Related
May 5, 2012
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)
[Code] .....
View 1 Replies
View Related
Sep 16, 2009
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: ...
View 8 Replies
View Related
Aug 2, 2014
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 Related
Aug 20, 2009
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 Related
Nov 29, 2011
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.
what is the easiest to do that?
View 1 Replies
View Related
Nov 21, 2008
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!
View 9 Replies
View Related
Aug 27, 2008
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?
View 8 Replies
View Related
Apr 20, 2008
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 Related
Jan 29, 2009
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.
View 6 Replies
View Related
Jun 5, 2007
After doing a search, I need to ask an Excel 2003 question. I'm creating a tool to open three sparate workbooks. One is the driver and I need to use it to pull data from the second and then to place that data along with additional data into the third.
My driver data may consist of a single value or multiple values separated by semi-colons in a cell. A single value works fine. The first value in a multi-value condition works fine. the second find, however, gives me an object error. I'm using the " split" verb to separate the values. Here's my code. Have you any idea why the second find is throwing up this error when the first find works correctly?
varData = Split(strRef, ";", -1)
For J = 0 To UBound(varData)
varSrchVlu = Trim(varData(J))
' do the ARIS Exrtact matching
Workbooks(strManualFile).Activate
Workbooks(strARISExtract).Activate ' Activate ARIS Extract
Sheets("Processes").Cells(2, 1).Activate
Workbooks(strARISExtract).Sheets("Processes").Range("A2").Select
Workbooks(strARISExtract).Sheets("Processes").Columns("A:A").Select
View 3 Replies
View Related
Nov 6, 2009
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 Related
Dec 26, 2012
Need to find the first cell above the active cell containing any sort of variable (integer, string value, cell fill colour, named range, etc...).
For example, a column contains multiple integer values between 1 and 10. I would like the macro to "locate" a specified value, say 3. Ideally the macro will select the first cell matching this criteria, located above the active cell. Once located, the resulting cell will be added to a range to be copy-pasted to a new worksheet.
I am planning to use this code for various applications in a workbook I am creating. At present the code will be used to locate string values, and cell fill colours. It is also likely this will extend to other types of variables in the future.
I've found information relating to the find function, but nothing specifically to find the first result above the active cell.
View 5 Replies
View Related
Feb 9, 2013
I'm trying to work on a macro that will copy two cell ranges (name of current month and prvious month) from one location, store it as a variable and use that variable to find replace similar text string in another tab. Below is my code which is not changing the values in another tab...
Dim OldMonth As String
Dim NewMonth As String
Sheets("X1").Select
[Code]....
View 2 Replies
View Related
Oct 4, 2008
I am still a novice in you standards, but have run into a problem and hoping you all will have some insight for me.
I have a workbook that I am parsing data from the first sheet and putting the data onto the second sheet. Before I place the values in I want to format the cells with boarders, colors, alignment, and in some cases validation (but I can worry about the validation later).
Currently what I am doing is using a 3rd page that has a group of cells that have the formats I want in place before I start. The code will then parse the data and when it finds the data I need go to sheet 3 and grab a range of cells and paste those cells to sheet 2 and then write the data over those cells. This allows me to format the document as I create it.
The problem is that I am going through 25k of lines and on average this function is running 40+ minutes. If I run the same script with out the formatting it only takes at most 2 minutes.
So on to the question.
Is there a way that I can start of my script by grabbing all the format information before I parse the data and save it as a variable? Then instead of doing a copy of the format it would just apply the formatting that is saved already. I would think this would be much faster of a process.
View 9 Replies
View Related
Aug 4, 2007
I am trying to create a macro (please look the attached excel what I have done), I have already crwated a macro that after 5 seconds is gonna change the background color of the of the cell en function of another cell.
That means if F3:F4 change the background color F8:is going to change the backgorung color also, every 5 seconds. The macro I wrote is also include in the excel attached file.
Now this is my problem... I have a variable cell that is give for D6 which could be A,B,C,D,E,F (Range F2:K2).
If the variable cell D6 is A the background color of F8:F9 should be the background color F3:F4 .
If the variable cell D6 is B the background color of F8:F9 should be the background color G3:G4 .
If the variable cell D6 is C the background color of F8:F9 should be the background color H3:H4 ......and etcetera.
View 5 Replies
View Related
Jun 17, 2008
If Not stfound Is Nothing Then 'if value is found then do this
is causing me a run time error 424 when I try and run my code "Object required". Entire code is pasted below...
Option Explicit ....
View 4 Replies
View Related
Sep 8, 2006
I need my program to:
- find the cell containing the string "Datum/Tid"
- record the column and the row of the found cell in two variables lCol and lRow
Here is my
Sub test()
Dim rFoundCell As Range
Dim lRow As Long
Dim lCol As Long
'Find method of VBA
Set rFoundCell = Range("A1")
Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
'for anyof the two lines down I get the message "object variable OR block variable not set"
lRow = rFoundCell.Row
lCol = rFoundCell.Column
End Sub
View 5 Replies
View Related
Nov 21, 2006
I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then End
startDate = Format(startDate, "dd/mm/yyyy")
stopDate = Format(stopDate, "dd/mm/yyyy")
startRow = Worksheets("sheet1").Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select
End Sub
View 9 Replies
View Related
Nov 7, 2013
I have a worksheet named "ABC". In row 4 of this worksheet, each cell from G to BG is numbered from 1 onwards and each cell has a unique number. I would like to have a macro ask the user through a pop-up question box which column data should be calculated in. For example, if the user types "1" in the question box, the macro will go to column G which has "1" in cell G4 and will select G5 the cell just below the column the user defined and calculate the formula.
View 6 Replies
View Related
Jun 3, 2008
I am using this program in one workbook to capture the datevalue in integer from another workbook which i opened. But the program as it reaches the line x2=Datevalue( Cells(2,14).Value) gives a Type Mismatch error.
Public Sub find_date()
Dim x2 As Long
'I am trying to activate the last opened file by using workbooks.count
Workbooks(Workbooks.Count).Activate
Worksheets("Sheet1").Cells(1, 1).Select
x2 = DateValue(Cells(2, 14).Value)
End Sub
Auto Merged Post Until 24 Hrs Passes;btw..the cells(2,14) has a date, formatted in the type of mm/dd/yyyy.
View 4 Replies
View Related
Aug 28, 2006
I have a spreadsheet form with all kind of values and what I want to do is to find a data in a worksheet named "Config" based on the spreadsheet activecell value. With the find row adress I return data of another column but same row (in the worksheet) in two diferent label captions. If the spreadsheet activecell value it was found in the worksheet everything is ok but if the value is not found I'm getting the message "Object variable or with block variable not set"....
View 7 Replies
View Related
Sep 28, 2007
I'll get straight to the point:
where ( Date > 9/20/2007)
The above Date si used in a sql select statement where I'm hard coding the date (9/20/2007). This date is actually located in sheet1, cell E1. How can I get it from that cell and use it in my Select statement instead of entering the date manually every day in my code?
View 5 Replies
View Related
