Stripping Out And Changing Time From Variable With VBA
Jan 31, 2014
I have a macro that needs to work with data including different time from different sources.some of the cells are date and some are text.
I have the following function
Public Function TidyDate(zdate) ' This is coming in as a date #01/02/03 08:00:00#
Dim TempVar1, Tempvar2 As String
TempVar1 = zdate
Tempvar2 = Left(TempVar1, 11) & "00:00:01"
TempVar1 = Tempvar2
[Code] ....
What i need is to change the time of the variable to 1minute past midnight but keep the date the same.
View 5 Replies
ADVERTISEMENT
Feb 20, 2009
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
View 9 Replies
View Related
Dec 7, 2008
I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.
View 3 Replies
View Related
Sep 11, 2013
In A1 I have 35 min elapsed time and need to change it to time as a portion of an hour. It reads the 35 min as TIME, therefore I am currently using
=IF(A1>=0.5,A1-0.5,A1)*24. Output is .58, which is perfect.
(output column formatted as a number)
Likewise 3:28 becomes 3.47.
View 1 Replies
View Related
Oct 7, 2009
I am having a lot of trouble finding out why I am getting error. I believe the error is because it can't find the number. In cells C115:C314 i have the numbers 1 to 200 in order. when someone types in 1 to 9 in the text box it works, but on 10 and over i get the error ???? here is the code I have
shCalculator.Range("C115:C314").Select
Selection.Find(what:=CInt(txtPackageID), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Select
x = ActiveCell.Row
shCalculator.Range("ProposedMeter").Value = Cells(x, 7).Value
shCalculator.Range("Package").Value = Cells(x, 12).Value
shCalculator.Range("ProposedMeterAmount").Value = Cells(x, 30).Value
shCalculator.Range("Term").Value = Cells(x, 62).Value
shCalculator.Range("Discount").Value = Cells(x, 67).Value
shCalculator.Range("Equipment").Value = Cells(x, 72).Value
View 3 Replies
View Related
Jan 14, 2007
Function Find_Range(Find_Item As Variant, _
Search_Range As Range, _
Optional LookIn As Variant, _
Optional LookAt As Variant, _
Optional MatchCase As Boolean) As Range
Dim c As Range
If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
If IsMissing(MatchCase) Then MatchCase = False
With Search_Range
Set c = .Find( _
What:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _ ........................
Using the message box I see that sheet 1 opens but then I receive an error message Run Time Error 91, Object Variable or With Block Variable not set. I tried declaring and using set on "project" but got nowhere. I also need to have a message indicating project not found. Once this part is solved I will loop all of my other workbooks
View 5 Replies
View Related
Apr 18, 2007
Sub Macro1()
Dim blue1 As String, blue2 As String, blue3 As String, num As Integer
blue1 = "one"
blue2 = "two"
blue3 = "three"
For num = 1 To 3
ActiveCell.Value = blue & num
ActiveCell. Offset(0, 1).Select
Next num
End Sub
What I am trying to achieve is to use the appropriate "blue#" variable according to the num variable. For example, when num = 1, I want the macro to use the variable blue1. I am not sure how I should code this.
View 2 Replies
View Related
Jan 9, 2008
I have a list of names that I need to separate into FIRST and LAST names. These names are located in column A. The problem with my initial list is that some of the names have two first names (in the format of Herman & Betty Altenfelder) and some just one first name (such as Tom Frank). Could someone please help me in writing a function that will place the first name(s) in one column (such as column B) and the last name in a different column (such as column C).
View 9 Replies
View Related
Jan 22, 2007
I have a range that can have numbers and text in it. I want to strip out anything before and after the number. For example. If any of the cells contain text such as "The price is $4.28 per pound" it would strip out everything except the 4.28. This cell may also contain Japanese yen so you can not use the decimal for reference.
View 14 Replies
View Related
Mar 18, 2013
I have a cell (A4) on my worksheet that displays 64421 but has a hyperlink associated with it.
I need to use the value in A4 in a vlookup formula, however, I am getting NA# errors when I use it.
Code:
=vlookup(a4,wshgroup("A1:Z500"),4,false)
I suspect that as a hyperlinked value, it does not actually hold the value 64421 needed to match in the vlookup range.
Any way to ensure that the value for A4 is consistent with the numeric data in the lookup range?
View 3 Replies
View Related
Feb 8, 2007
I have a text file that has been saved via PL/ SQL code from a CLOB (due to the MSDAORA not supporting CLOBs). Unfortunately, due to passing the file between operating systems it appears that the CR and LF characters are not consistent. This means that my third party application in Excel cannot read the text file properly.
View 2 Replies
View Related
Jan 17, 2009
I have some code that uses offset to select a column of numbers
View 2 Replies
View Related
May 11, 2009
I am trying to find the max value within a range (14 cells) contained in one column ("C" in this case). This range will change corresponding to the current cell. Ex. as the current cell moves down (or increases in number) the range of cells to find the Max in moves down (or increases in number) as well. The Max value will then be used as a variable in a equation but for now just getting it into a cell is fine. This seems like it would be easy but it is driving me absolutely batty!
I currently have the non-working code located in a For loop that contains other calculations as well. I've taken those calculations out since they work fine but cloud the bad part. So far I have tried:
View 3 Replies
View Related
Nov 28, 2012
I'm taking some university classes and one of them is called Applications Programming and we have been using excel VBA. I have a project that I'm working on, and it's very basic. Basically we need to create a userform that has 3 option buttons, 4 check boxes, a couple labels and a couple text boxes. It's suppose to be a ice cream selection terminal. Option boxes for basic flavors of ice cream, check boxes for toppings, one text box for user input of number of scoops of ice cream and the last text box to output a reciept/overview of purchase.
So here's the question... I was wondering if there's a way to change the font of a single string variable, or change the font of a word within a string. My output to the saleBox(My receipt overview) is this:
saleBox.Text = ("Thanks you for your Purchase") & vbCrLf & vbCrLf & _
"You ordered " & numOfScoop & " scoops of " & flavorChoice & "ice cream" & vbCrLf & _ "Topped with: " & toppings & vbCrLf & vbCrLf & total
(not sure how the code is gonna look in the message but I'm sure you get the idea)
i want to be able to change the font for the flavorChoice or toppings variables. They are both strings.
Anyways, I know I can get away with basically what I have there and get full marks, but I like going above what the basics are. If it's kinda complicated.
I'm gonna attach a screenshot, more info is better than not enough right... nevermind its askin for a url!
*** I just now got the idea of putting another textbox over the area that would output the text I want to change and hide the border or something and then change the font of that text box through the properties ***
View 3 Replies
View Related
May 20, 2006
Trying to code my first custom VBA function. The function is supposed to translate the words in a cell by repeatedly looping through the string and looking through a list of provided translation words.
Problem is the variable name changes with each loop, and I can't figure out how to program the name change.
Function TestTranslate(TranslateCell, ProductCell) As String
Dim t As Long
Dim TranslateCeiling As Long
Dim SS1 As String
Dim SS As String
Dim Q, R As String
Dim W As String
Dim LLRString, MLRString, SLRString As String
Dim LRString, MRString, SRString As String
Dim TArray() As String
Looks like the loop keeps defining Q as "SS" & t instead of doing the select case formula. I tried removing the Q variable altogether and making it read just "SS" & t = Replace(R, LLRString, LRString) for example and keep getting a compile error.
View 9 Replies
View Related
Sep 18, 2009
Here is a simple exercise that is necessary only because of the large amounts of data involved.
I have a spreadsheet of columns with budget and actual data. I need to strip out the budget columns (marked as budget) and total column. then add back in at a later date.
It all has to stay in the same spreadsheet.
I know it sounds strange but believe me its necessary.
I was thinking of moving them to the right side of the spreadsheet. Then back at the later step.
I have attached a simple spreadsheet showing the data on sheet1 and how it should look when stripped on sheet 2. and the later step would move them back to sheet3(which is the same as sheet 1).
View 14 Replies
View Related
Aug 29, 2006
I'm working on an attendance spreadsheet that shows the employee's absence using the format "X####", where X can be one of the following three values: S(for sick time), P(for personal time), and V(for vacation); "####" is the number of hours absent (can be from 0.25 to 8.0,measured in 15-minute increments, # can be either a digit from 0 to 9 or a decimal point, and there can be from 1 to 4 characters, but must be >0(I'm not worried about data validation yet).Examples are P8.0, V.5 (V0.5), S2.75,etc.
The spreadsheet is laid out such that each row represents an employee, and each column is a workday. After the employee's information are three columns labeled P,V, S. I'm trying to generate a formula that when applied to an employee's row, will display a running total of the absent hours based upon personal, vacation, or sick time taken. We'll call the range "CAL"(Short for calender)
To strip out the numeric characters on the right, I use the formula RIGHT(CAL, (LEN(CAL)-1)). To Determine which column the data should be summed in, I use the formula LEFT(CAL,1). Blank Cells are permitted, in fact most of the row will be blank. Now that I've figured out how to strip out the alpha from the numeric, how can I get the totals? I apologize for writing "War and Peace," but I wanted to be as concise as possible-
View 5 Replies
View Related
Apr 18, 2012
how to or if it's possible to change the font color of a string variable? or is there any workaround?
View 2 Replies
View Related
Jun 20, 2007
I am pasting vlookup formulas into a spreadsheet using a macro, and want to change the reference column number based on a variable generated within the code eg
= vlookup(RC1,table,i,false) where i is a predetermined variable in the code
for i=23, I need the result to be of the form
=vlookup(a1,table,23,false)
View 7 Replies
View Related
May 23, 2008
I'm trying to figure out a way to lookup a value based on a conditional sheet name. I'm finding it difficult to explain in words what I'm trying to do, so I'm going to try and describe it in a miniature example of my spreadsheet. I apologize if this is going against forum rules, this seems like the quickest way to get my question across:
The following is the way my spreadsheet's first page is set up:
ID Apr-08 Mar-08 Feb-08
1
2
3
The column labels depend on another table, which changes monthly.
The rest of the sheets (24 of them, labled "200804", "200803", etc) look like this:
ID $ amount Date paid
1
2
3
I have another table upon which the na
I'm trying to return the "Date Paid" value for each ID for each month. Each month, however, more data arrives, and the labels change. I have several dozen spreadsheets formatted in this way, and I'm trying to avoid having to change the vlookup (or other formula) manually each month for each sheet. Is there a way to make the vlookup depend on a table?
This is essentially what my formula looks like right now for cell A2:
=VLOOKUP($A2,'200804'!$A$1:$G$10000, 3, FALSE)
If I could replace '200804' with a cell reference, that would be ideal. I haven't been able to figure out how (if possible); I tried experimenting with index sheets but that went nowhere.
View 9 Replies
View Related
May 28, 2008
I have a vast database where I have linked charts. I send the data from a macro to the database and sometimes there are 1000 inputs and sometimes there are only 20.
How would i set up my charts to only graph the inputs that are present?
View 7 Replies
View Related
Apr 11, 2006
I have a column that's supposed to be an address, but somehow got the
company name mixed in at the beginning:
123 Main Street
One Park Place
Acme Company, Inc. 456 Easy Street
Alpha-Omega Dry Cleaning Four-Twenty Highway One
I can use the formula
=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")-1)))))
for stripping the text before an actual number
Acme Company, Inc. 456 Easy Street
to
456 Easy Street
BUT - I'm not sure how to strip before a text representation of a
number (one, two, three, etc)
Alpha-Omega Dry Cleaning Four-Twenty Highway One
to
Four-Twenty Highway One
View 10 Replies
View Related
Apr 18, 2006
I have to create a formated file to test our system every other week, I want to create a File Generator using Excel and the replacement values forumla "& A1 &". However the file is in a CSV format containing a lot of quotes and when I attempt to use a line of the file in a formula with "& < Cell> &" excel reports errors, if you accept excels help it strips out the quotes and commas. Anyway around this? having to have a user save the file is CSV format would be to complicated. Sample file attached.
View 2 Replies
View Related
May 14, 2014
Here's what I'm doing: I'm using a macro to assign a cell value to a variable then set another cell value to the variable instead of copy/paste (because even pasting values only was affecting other formulas in the file for some reason)
The problem: in using the macro, the number being 'copied' is acquiring a few extra decimal places IE 38334.61 is the original number and 38334.609375 is what I end up with. The numbers come from a CSV with only 2 decimal places and I checked by adding decimal places in the format so it's not a formatting/visible digits issue. Since the values are hour meter readings, the extra decimal places end up with very small values outside 0-24 hrs which messes with sorting and usage %. The values are so small all of them together in a month add up to a fraction of a cent but it's one of those things that bugs my OCD by not being right.
My VBA book explains the min/max capabilities, content type, memory bits but not fiddly details like this.
So my question is this: right now I'm declaring the clipboard-substitue variable as an single, is there a different one that would work better without adding anything? Criteria are: numeric, 6 digits before the decimal, 2 after, all positive values. (Ie 123456.12)
EDIT: fixed my senior moment.
View 6 Replies
View Related
Jun 6, 2014
Look at spreadsheet, calculating time off.
My problems are in columns (k) total amount of days off, (l) weekday days off, and (m) weekend days off
Columns n & o are the results that my employers computer delivers on that amount of time off!
I have provided at the end off my spreadsheet data, some info on the rules that govern how this is calculated.
View 1 Replies
View Related
Aug 23, 2007
I am trying to enter 00:00:00.031, but it is changing it to 00:00.0. Can someone please tell me how to stop it from doing that?
View 9 Replies
View Related
May 6, 2009
I am looking for a way to simply change the values in column B (start time) back by one hour, for instance 06:00:00:00 would become 05:00:00:00. Because this schedule is a broadcast schedule, time is shown in 4 fields hour, minutes, seconds, and frames (hh:mm:ss:ff).
I can't find a way to quickly adjust it, and because my company broadcasts in a lot of different timezones, I spend a lot of time manually adjusting the values myself. It seems frustrating because I know excel recognizes the values, because it is capable of sorting them, of course that may mean absolutely nothing. I have attached a sample file that I hope should make my problem a little clearer.
View 5 Replies
View Related
Dec 7, 2013
I have to create a number of spreadsheets with changing dates / times / both.
This would be a fairly good example:
Add one day + 1 hour for 31 days of a month.
"01/01/2014 01:10"
"02/01/2014 02:10"
"03/01/2014 03:10"
"04/01/2014 04:10"
"05/01/2014 05:10"
"06/01/2014 06:10"
"dd/mm/yyyy hh:mm"
The above format is important, and - it has to be in quotes.
Which, if necessary I will manually add using replace, but would prefer not to.
So I may then need to take the first date... lets say 06/12/2013 07:00 then, Add one day + 3 hrs 30 mins.... so as the days roll between 1 to 31 it is obviously going into the 24 hour clock, rolling argument.
And, to top that -
1. the csv then needs to be put into software (I assume it reads the data in the sheet and not the formulas).
2. I can't have extra columns as they would need to be deleted.
I have tried copying down, like a standard auto-fill but it always goes wrong.
View 3 Replies
View Related
Aug 29, 2008
I have a column that shows the date and time and it looks like this:
8/1/2008 6:36 AM
8/1/2008 11:15 PM
8/1/2008 8:01 PM
8/1/2008 3:12 AM
I want to convert it to show just the time but I want it to be in 30 minute increaments. So in the example above, I'd want to see this:
06:30
23:00
20:00
03:00
View 9 Replies
View Related
Mar 30, 2009
When I enter this : =now(). it gives the local date and time in my PC in that exact cell. assume it is in (A2). when I enter in Any cell in the spreedsheet the sam formula =now(). then the previous formula (A2) is changed to the new date and time.
View 5 Replies
View Related
