Error with distribution code

[Nisha_NishaDelhi]

Dear VBA Experts
I have written a code for equal distribution of Budget Data based on condition.

The user will enter annual budget number in Column K (highlighted in Green) and based on condition in Column N (highlighted in Yellow), the data will be divided by 12 and it need to be equally distributed from Colum P to Column AA. The condition for distribution is ‘Y’ (Yes)/ ‘N’ (No).

[Sub BudDist()
Dim myCell As Range
Dim Data As Long
Dim lr As Long
Dim a

lr = Cells(Rows.Count, 11).End(xlUp).Row
For Each myCell In Range("K39:K" & lr)
If UCase(myCel.Offset(, 3).Value) = "Y" Then
Data = myCell.Value / 12
a = myCell.Row
Range("P" & a & ":AA" & a).Value = Data

Next myCell
End Sub]



Error.jpg


Please help me fix the error. The screenshot of the error is attached.

Thanks,
Nisha

[PCI]

Is missing the "End If"

[shg]

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

[Nisha_NishaDelhi]

Hi PCI,
Thanks for your quick reply.

I added the "End If", but still gettin error.Since I have basic knowlede of VBA, I will sincely appreciate if you could help me fix the error. My code after adding "End If' is given below. In addition, I have also added the screenshot of the new error I am getting.

Please Login or Register  to view this content.

Error-1.jpg

Thanks,
Nisha

[PCI]

It will easier to have a sample of your file.
Use the Option explicit at the beginning of your code
Use myCell every where not myCel

[Nisha_NishaDelhi]

Hi PCI,

I tried the option you suggested, but unfortunotelly it is not working. Also, I was not getting any optionn to attach file in this post, hence I have started new post with file attached. I hope it will be helpful for you to help me resolve the issue.

Thanks a lot for your effort and time.
Nisha

[PCI]

Nisha,
To attach a file:
Go Advanced
Manage Attachments
Add Files
Basic Uploader
Browse
==> select your file
Upload
==> your file appear in the Attachments window)
Done
Did you watch the myCell variable every it is used: "myCel.Offset(, 3).Value)"

[PCI]

Nisha,
Reading your new thread you have understood :
How it's easier to see the issue with a file attached
The problem was coming from space with the letter "Y"
The Trim function is removing these spaces
See
http://www.excelforum.com/excel-prog...tion-code.html