VBA math and firstdayofweek

jbesant

New Member
Joined
Jun 8, 2010
Messages
23
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
First day of week:

Code:
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

New Member
Joined
Jun 8, 2010
Messages
23
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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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:
Code:
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

New Member
Joined
Jun 8, 2010
Messages
23
Hi p45cal - appreciate the advice on the type mismatch error. It helped me find what was wrong with my code. Thanks!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,046
Messages
5,767,822
Members
425,437
Latest member
blaix

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