Month function

Question

QUESTION: part of my code was worked properly after applying your recommendations except for the below code. Suppose that in Column "D" there is different dates (e.g. "01/03/2010") and I want if there column D is empty we will do no thing, but if it is not, the month function (e.g. Y4 = Month(D4)) will be applied in Column Y. I meant by the below column offset of -17 from column Y is D not H, and I don't know if I used the correct number.

For Each cell In Sheet11.Range("Y4:Y1000")

  ' a column offset of -17 from column Y is column H.  I have no idea what is
  ' in column H
      If Application.WorksheetFunction.CountA(c.Offset(0, -17)) > 0 Then
          ' so the first time you find a value in column H, you use it
          ' to put a month number in column Y, then exit your loop so
          ' if column Y was empty before it has one value in it now??
          cell.Value = Month(c.Offset(0, -17))
          Exit For
      End If
  Next cell

Would you mind helping me to write the correct code to apply Yi = Month (Di), please?

Hamdy

ANSWER: Hamdy,

If you want to do it for all rows that have a value in column D, then

For Each cell In Sheet11.Range("Y4:Y1000")

 ' a column offset of -17 from column Y is column H.  I have no idea what is
 ' in column H.  -21 from column Y is column D
     If Application.WorksheetFunction.CountA(c.Offset(0, -21)) > 0 Then
         ' so the first time you find a value in column H, you use it
         ' to put a month number in column Y, then exit your loop so
         ' if column Y was empty before it has one value in it now??
         cell.Value = Month(c.Offset(0, -21))

     End If

Next cell

Just to demonstrate that -21 is the correct offset, this is copied from the immediate window in the VBE

? range("Y4").Offset(0,-21).Address $D$4

so you see that an offset of -21 from column Y is column D.

---------- FOLLOW-UP ----------

QUESTION: When I have tried to apply the code, the following debug error massage is appeared (Run-time error '424': Object required) and the following code line is highlighted by yellow

If Application.WorksheetFunction.CountA(c.Offset(0, -21)) > 0 Then

Thanks Hamdy

Answer

Hamdy,

I modified the part of the code you asked about (the offset and processing multiple rows). That line of code was written by you or someone else incorrectly to begin with.

that is why you are getting an error. In that line, you are using the variable c. However, you probably should be using the variable cell which is what you are using to loop with.

For Each cell In Sheet11.Range("Y4:Y1000")

' a column offset of -17 from column Y is column H.  I have no idea what is
' in column H.  -21 from column Y is column D
'    If Application.WorksheetFunction.CountA(c.Offset(0, -21)) > 0 Then '<== bad line
   If Application.WorksheetFunction.CountA(cell.Offset(0, -21)) > 0 Then  ' replacement line

        ' so the first time you find a value in column H, you use it
        ' to put a month number in column Y, then exit your loop so
        ' if column Y was empty before it has one value in it now??
        cell.Value = Month(c.Offset(0, -21))

    End If

Next cell