Thread: somebody help! iam going insane

What am I doing wrong here...

Below is the code of the macro ..
All I want to do is
execute the macro which brings two input box asking the user with the start year and number of years. ie years

I want to match the years with the year in column C from the 'data' worksheet . if the year matches, select that row then I need to check if column B of that row=3. If Col B =3, then paste that row from E:Z and 11 rows below into 'test' workksheet.

In this code, the value of col B=3 is not checked correctly and is pasting wrong values into 'test' worksheet.

Please help.

For i = 1 To Years
'Copy the last worksheet
With ThisWorkbook.Worksheets
.Item(.Count).Copy After:=.Item(.Count)
End With
'Rename the Worksheet with the year
ActiveSheet.Name = "Year1" & Str(StartYear + i - 1)


'Column "A" has Year
Windows("data.xls").Activate
Set Y = ActiveSheet.Range("C:C").Find(StartYear + i - 1)

If Y Is Nothing Then
MsgBox "Year " & YTF & " not found."
Exit Sub
End If
Set RngY = ActiveSheet.Range(Cells(Y.Row, "B").Address, Cells(Y.Row, "Z").Address)
'ActiveSheet is Sheet1
Do Until ActiveSheet.Cells(RngY.Row, "B").Value = 3
Set RngX = ActiveSheet.Range(Cells(RngY.Row, "E").Address, Cells(RngY.Row + 11, "Z").Address)
RngX.Copy

' Destination

Windows("test.xls").Activate
ActiveSheet.Range("C4").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

Exit Do
Loop

Next i

Does my post that slipped in seconds before your post take care of this (I was answering your prior question that just showed a subset of this macro) or do you have another issue?

Are you getting the Type Mismatch on the line of code I provided or the line immediately below which starts with Set RngX? Just want to make sure I'm looking at the right spot.

No I am not getting any mismatch ... i am not getting any output in the sheet. no copying and pasting is taking place.

Is the code identifying a row where the cell = 3 and then moving on to the next line of code: Set RngX = ActiveSheet.Range(Cells(RngY.Row, "E").Address, Cells(RngY.Row + 11, "Z").Address)? If is does move to this line, is it actually selecting the range you want it to? You can see this by stepping through the macro using F8 in VBA. I think there is an issue with the line of code included above, but can't be sure without seeing the entire code. If this line isn't selecting the necessary range, I can fix it really quick - Chad

Yes, It is going through both the lines, It is giving me the wrong output numbers...

The problem I have is , It finds the row -year entered by the user and then the first row with the year has B=1, so it copying that values and giving me .It is not checking or copying the rows where the year = year entered by user and also B=3... thats the problem...
instead it is giving me the data where B=1 as output .


let us say the table is

A B mnth_id D
xx 1 1 2008 ( entered by user when prompted
xx 1 2 2008
(after 10 more lines)
xx 2 1 2008

(after 11 more lines- for the year)
xx 3 1 2008
this is what i want.


It copies the 12 months values for 2008 when B=1
But all I want is the values for 2008 when B=3
which values