VBA math and firstdayofweek

[jbesant]

Hi there,

I've got two questions today:

First: How can I automatically return the first day of the week in vba. I want to do this completely without any user input, i.e. how can I properly code this:
Format(FirstDayOfWeek, "mmdd").

Second:
How do you multiply and divide cells using vba? I thought this would have been easy, but I'm getting a type mismatch when i try this:
For j = 1 To 3
Do
x = CDbl(Cells(i, j))
y = CDbl(Cells(i, j + 4))
z = CDbl((x - y) / x)
Cells(i, j + 8) = z
i = i + 1
Loop Until IsEmpty(Cells(i, j))

Next j


Any help would be very much appreciated!
Jamie

 

[gaj104]

First day of week:

myDate - weekday(myDate,3)

Dividing etc:

have you made i = 1 to begin with

cells (0,1) is not a valid reference from the beginning of your loop.

 

[jbesant]

Hey thanks for the response on the date question. Much appreciated!

Yes I did set i = 1 as I noticed that the reference would be otherwise invalid. i think the problem is with the type mismatch. Perhaps cells (x, y) returns something different than the range function, and that is the source of the error? Any ideas?

 

[p45cal]

trying to answer the second part, I think it depends just what's in the cells I had the type mismatch error if I had text in one of the cells), trying it here with numbers in the cells works fine here but as an aside, the code failed when it got to the bottom of the list of numbers with an Overflow error, I suspect you might want the code to be:

i = 1
Do
  For j = 1 To 3
    'Cells(i, j).Select
    x = CDbl(Cells(i, j))
    'Cells(i, j + 4).Select
    y = CDbl(Cells(i, j + 4))
    Z = CDbl((x - y) / x)
    'Cells(i, j + 8).Select
    Cells(i, j + 8) = Z
  Next j
  i = i + 1
Loop Until IsEmpty(Cells(i, j))

(with a few debug lines to select cells commented out)

 

[jbesant]

Hi p45cal - appreciate the advice on the type mismatch error. It helped me find what was wrong with my code. Thanks!