I am trying to take a string (specifically one in the format "2008-09-01 00:00") and write an if statement to perform a process if the value to the right of the rightmost colon is anything but "00"
I have extracted the text from the cell using the code: - For h = 2 To NumberOfRows
-
Cells(h, 1).Select
-
Dim v As Variant
-
Mytext = Cells(h, 1).Text
"Mytext" displays "2008-09-01 00:00"
I do not know how to use a colon as a delimiter to find the value of the last two values in the string. Would I use the "Split()" function?
Yet another approach should you have multiple Colons in the String: - Dim strTest As String
-
-
strTest = "2008-09-01 00:100"
-
-
'See if the Value to the right of last Colon <> "00"
-
If Mid$(strTest, InStrRev(strTest, ":") + 1) <> "00" Then
-
'Process Code here
-
End If
11 4565
Split could be used. You could also use InStrRev in conjunction with a Right.
Split( ) actually returns an array, so you need to Dim an array and then do the assignment... - Dim arr() As String
-
arr = Split(MyText, ":")
You can then reference the array elements as you wish.
Pat
If you only need to use it once and you know which element you want, you can do this
Yet another approach should you have multiple Colons in the String: - Dim strTest As String
-
-
strTest = "2008-09-01 00:100"
-
-
'See if the Value to the right of last Colon <> "00"
-
If Mid$(strTest, InStrRev(strTest, ":") + 1) <> "00" Then
-
'Process Code here
-
End If
NeoPa 32,556
Expert Mod 16PB Austin Redd:
I do not know how to use a colon as a delimiter to find the value of the last two values in the string. Would I use the "Split()" function?
I'm not sure what you mean by the value of the last two values in the string, as you've only explained a requirement to see the value after the colon. I guess you probably mean last two digits. If so, the following code, as explained by Rabbit in post #4, will give you access to that : @NeoPa: that logic would not work for the 'rightmost' Colon, as indicated by:
write an if statement to perform a process if the value to the right of the rightmost colon is anything but "00"
This would indicate to me that there can be more than 1 Colon in the String. Off Tangent again? (LOL)
- Dim LastCharacters As String
-
-
For i = Len(MyText) To 1 Step -1
-
If Mid(MyString, i, 1) = ":" Then 'The character number i is ":"
-
LastCharacters = Mid(MyString, i+1)
-
Exit For
-
End If
-
Next i
-
-
Debug.Print LastCharacters
NeoPa 32,556
Expert Mod 16PB
That's good critical thinking ADezii, but in this case I expect it's just a case of the OP being unable to express their question clearly enough. Notice the following : Austin Redd:
(specifically one in the format "2008-09-01 00:00")
I worked on the basis that what they'd meant to say was : write an if statement to perform a process if the value to the right of the colon (found towards the right-hand end of the string) is anything but "00"
If they hadn't already stated specifically what the expected format was then your querying this would be well worth raising and the code maight well need to be done differently. This code only handles the minutes value and never any seconds value if that were to be included.
If the format of the string is always as shown in the OP's example, then I really think this is a simple matter of using Split( ) in the manner that Rabbit indicates in post 4.
On the other hand, if the format has a varying number of delimiters, in some fashion that the OP has not pointed out to us yet, then it makes good sense to use an array as I show in post 3. This way, regardless of whether the string splits into two, five, or fifteen smaller parts (or whatever), you can always just reference the last element of the array to obtain the rightmost part of the parent string.
Pat
NeoPa 32,556
Expert Mod 16PB
As the OP used the word explicitly in describing the format of " 2008-09-01 00:00" I doubt that is the case. Assuming it were however, you might instead use : - Split(MyText, ":")(UBound(Split(MyText, ":")))
You could store it into an array first to save running the function procedure twice.
Thank you ADezii. That gave me what I needed to work with. The code I used was this: - 'find the last row in the "A" column
-
numberofrows = Range("A65536").End(xlUp).Row
-
'write a for statement to iterate through every row except for the column header row (A1)
-
For h = 2 To numberofrows
-
-
'Use my iteration variable "h" to search through the text in each cell
-
Cells(h, 1).Select
-
Dim MyText As String
-
MyText = Cells(h, 1).Text
-
-
'If the last two digits in "MyText" are not "00"...
-
If Mid$(MyText, InStrRev(MyText, ":") + 1) <> "00" Then
-
-
'Place zeros at the end of the two cells above the "Hth" row
-
'Use these cells to preform an autofill operation
-
'This replaces the last two values in every cell below
-
Cells(h - 2, 1).Select
-
Dim MyText2 As String
-
MyText2 = Cells(h - 2, 1).Text
-
NewText = Mid$(MyText2, InStrRev(MyText2, ":") - 13)
-
Cells(h - 2, 1).Value = NewText & ":00"
-
Cells(h - 1, 1).Select
-
Dim MyText1 As String
-
MyText1 = Cells(h - 1, 1).Text
-
NewText2 = Mid$(MyText1, InStrRev(MyText1, ":") - 13)
-
Cells(h - 1, 1).Value = NewText2 & ":00"
-
Range(Cells(h - 2, 1), Cells(h - 1, 1)).Select
-
Selection.AutoFill Destination:=Range(Cells(h - 2, 1), Cells(numberofrows, 1)), Type:=xlFillDefault
-
End If
-
Next h
Sign in to post your reply or Sign up for a free account.
Similar topics
by: AMT2K5 |
last post by:
Hello, how would I go about breaking up a string that is returned by a
function. After I do that, I will strcpy that data to a class data
member .
I have the following functions
void...
|
by: jw56578 |
last post by:
I've seen some system sp's that have a semi colon and number after the
the name such as
create procedure sp_procedure_params_rowset;2
what does this do?
|
by: Raquel |
last post by:
Could someone explain to me what the reason is for having a character
delimiter (which is double quotes by default) for performing
Loads/Imports on UDB? I would think that column delimiter along...
|
by: Raj Kotaru |
last post by:
Hello all,
I recently came across the following segment of code that defines a C
struct:
typedef struct
{
unsigned char unused_bits:4;
unsigned char wchair_state:2;
} xyz;
|
by: Calvin Lai |
last post by:
Hi all,
I was developing an web application where the data in the client are stored
in cookies using the Response.Cookies collection. However, I have found out
that for some clients, their...
|
by: Dan Lenski |
last post by:
Hi all,
I'm a recent, belated convert from Perl. I work in a physics lab and
have been using Python to automate a lot of measurement equipment
lately. It works fabulously for this purpose. ...
|
by: martinskou |
last post by:
Hi, I'm looking for something like:
multi_split( 'a:=b+c' , )
returning:
whats the python way to achieve this, preferably without regexp?
Thanks.
|
by: smugcool |
last post by:
HI ALL,
I am having multiple .csv files in a single directry. I wanted to import all these files in a excel file with semicolon delimeted by a single click of a button. Can anyone please help me...
|
by: nigel |
last post by:
Hi,
I'm using VBA to export data from a table direct to a CSV file
DoCmd.TransferText acExportDelim, , "ExportTable", filePath
this produced a file with COMMA separated values,...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |