CODEWITHSUNDEEP
excelvba
Eddy
Eddy

Reputation: 11

I need to assign time value to a variable

I need to assign a variable time value that is in particular cell.

I have tried assigning values differently but everytime it gives me mismatch error:

Sub country_despatch(i)

Dim tm As Double
Dim date1 As Double

Dim lRow As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet

lRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
While i < lRow

 date1 = Sheet1.Cells("B", i).Value
 display (date1)
Wend

End Sub

I need to assign cell B2 value to date1. If the condition is met we move to next cell B3 and assign its value to date1 so on till the end of the rows.

Upvotes: 0

Views: 116

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

There are several issues in your code:

  1. Row counting variables need to be Long because Excel has more rows than Integer can handle:
    Dim lRow As Long

  2. Your parameter i has no type specified:
    Sub country_despatch(StartRow As Long)

  3. In Sheet1.Cells("B", i) you mixed up rows and columns the correct syntax is
    Cells(row, column)
    Sheet1.Cells(i, "B")

  4. You use While i < lRow … Wend but never increment i so this loop will run endless. Either use a For iRow = StartRow To LastRow … Next iRow loop that auto increments iRow or increment i manually by i = i + 1 in your While loop.

  5. I highly recommend to use Option Explicit and use meaningful variable names. If you need to start numbering variables you did something fundamentally wrong. Names like ws1 and ws2 are the worst choice. Use something meaningful like wsSource and wsDestination for example. This is much easier to understand and maintain, and you will have less errors if you don't mix up ws1 and ws2.

    Also meaningful index variable names are extremly helpful. If you just name them i you never know if this is for row increments or column increments. If you name it iRow or iColumn it gets pretty clear.

So you should end up with something like this:

Option Explicit

Sub Test()
    country_despatch StartRow:=2
End Sub

Sub country_despatch(ByVal StartRow As Long)
    Dim MyTime As Double
    Dim MyDate As Date

    Dim LastRow As Long
    LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row

    Dim iRow As Long
    For iRow = StartRow To LastRow
        MyDate = Sheet1.Cells(iRow, "B").Value 'value into date format
        MyTime = CDbl(MyDate) - CLng(MyDate) 'extract only the time from a date/time

        Debug.Print "date", MyDate, CDbl(MyDate)
        Debug.Print "time part", MyTime
    Next iRow
End Sub

Make sure the values in your column B are real dates or times. If they are strings this might not work properly (or at all).

Upvotes: 2

user2648008
user2648008

Reputation: 152

change this date1 = Sheet1.Cells("B", i).Value to date1 = Sheet1.Cells(i, "B").Value

you had swapped the row index and column index to Cells

Upvotes: 0

Related Questions