Having trouble with time

[Milton Seymour]

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

 

[Joe Was]

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]

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]

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:

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.