Having trouble with time

Milton Seymour

New Member
Joined
Sep 6, 2006
Messages
1
Thank you for your time reading my post. I am new to VBA (very new) and I am trying to solve a problem involving assigining a numerical value to a specific time. I want a VBA program that will take a range of cells and return the pre-determined numerical value for each of the different cell values, mostly time. Example: every time it finds 12:00:00AM i would like it to return the value 5. Any help would be greatly appriciated.



Thank you
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
This only works if the cells are formatted as Text!

Sub myFindTime()
'Sheet module code, like: Sheet1.

For Each Cell In Range("A1:K77")
If Cell.Value = "12:00:00AM" Then Cell.Value = 5
Next Cell
End Sub
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Milton Seymour
Welcome to the Board !!!!!

What do you mean by "return" ? A popup or changing the value in the cell.
Find+Replace is my first thought, but not sure since you're talking about VBA ...

kind regards,
Erik
 

PolarBear

Board Regular
Joined
Sep 26, 2005
Messages
215
Not sure if I understand correctly, but I'll take a stab at it...

I had a similar problem, and I came up with two solutions. The first is if you are looking to match a value exactly. Use the Select Case statement. Here's what I used:
Code:
shtOne.Activate
 LastRow = shtOne.Cells(Rows.Count, "A").End(xlUp).Row
 For i = 2 To LastRow
    CntryID = Range("A" & i).Value
    CatID = Range("D" & i).Value
    Select Case CntryID
        Case "USA"
            DataRow = USRow
        Case "Canada"
            DataRow = CanRow
        Case "Japan"
            DataRow = JapRow
        Case "Australia"
            DataRow = OzRow
        Case "Austria"
            DataRow = AustriaRow
        Case "France"
            DataRow = FrogRow
        Case "Germany"
            DataRow = KrautRow
        Case "Netherlands"
            DataRow = DutchRow
        Case "Switzerland"
            DataRow = SwissRow
        Case "United Kingdom"
            DataRow = UKRow
        Case Else
            dummy = i
    End Select
' Now have to find the categories by searching within the CatID string
    If InStr(1, CatID, "PROSPECT MAIL", 1) = 1 Then
        shtTwo.Cells(DataRow, 15 + (DayNum - 2) * 10).Value = shtOne.Range(colData & i).Value / 1000
    End If
    If InStr(1, CatID, "PROSPECT PMFU MAIL", 1) = 1 Then
       shtTwo.Cells(DataRow, 16 + (DayNum - 2) * 10).Value = shtOne.Range(colData & i).Value / 1000
    End If
    If InStr(1, CatID, "PROSPECT TELEMARKETING", 1) = 1 Then
        shtTwo.Cells(DataRow, 17 + (DayNum - 2) * 10).Value = shtOne.Range(colData & i).Value / 1000
    End If
    If InStr(1, CatID, "PROSPECT WEB", 1) = 1 Then
        shtTwo.Cells(DataRow, 18 + (DayNum - 2) * 10).Value = shtOne.Range(colData & i).Value / 1000
    End If
    If InStr(1, CatID, "PROSPECT MEDIA", 1) = 1 Then
        shtTwo.Cells(DataRow, 19 + (DayNum - 2) * 10).Value = shtOne.Range(colData & i).Value / 1000
    End If
    If InStr(1, CatID, "CUSTOMER MAIL", 1) = 1 Then
        shtTwo.Cells(DataRow, 12 + (DayNum - 2) * 10).Value = shtOne.Range(colData & i).Value / 1000
    End If
     If InStr(1, CatID, "CUSTOMER WEB", 1) = 1 Then
        shtTwo.Cells(DataRow, 14 + (DayNum - 2) * 10).Value = shtOne.Range(colData & i).Value / 1000
    End If
     If InStr(1, CatID, "CUSTOMER TELEMARKETING", 1) = 1 Then
        shtTwo.Cells(DataRow, 13 + (DayNum - 2) * 10).Value = shtOne.Range(colData & i).Value / 1000
    End If
   
 Next i '
What the Select Case does is look for an exact match, and then executes the code underneath. How you proceed depends on your data; if sometimes it's a time, sometimes a number, and sometimes text, you will have to declare your variable as Variant type. If it's always a time, you will save space and execution time by making it a Date type.

The second block of code looks for a match inside a target string using the InStr function; I don't know if this applies to you or not. But either way should work, if I understood what you are trying to do.
 

Forum statistics

Threads
1,136,354
Messages
5,675,291
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top