VBA Code Question

[Excel_Assis]

Could someone please help with the following.


For i = 37 To .Range("B" & .Rows.Count).End(xlUp).Row
If .Range("E" & i).Value <> "" Then
.Range("E" & i).Offset(0, -1).Value = .Range("E" & i).Value
End If



The code works to line starting with For i = 37 To .Range
then skips over the rest. It all worked fine on another spread sheet.

 

[CharlesChuckieCharles]

For i = 15 To .Range("B" & .Rows.Count).End(xlUp).Row


XLUP appears counter to what you are attempting now ( since you've moved the start to the top of the range?

 

[Excel_Assis]

Hi Charles very good question just trying different things to see what works. Please correct me if I am wrong but For i = 15 means the number of rows down the range is looks correct? or an I wrong here.

I do not understand why it works on another sheet but not this one. I am getting more and more frustrated by the day.

 

[CharlesChuckieCharles]

What I'm saying is originally you were going from row 37 Upwards to row 11 (using XlUp) to get you row count

thats why i suggested "STEP -1" in the for loop to count down from 37 to 11 one row at a time

you've now moved your for loop to start at 15 ( the top of the data instead of the bottom?) so should you be using xldown to get your row count ?

 

[Excel_Assis]

Charles all i know is if I change the For i = number from 11 in sample 1 I lose one of the unique cat records.
As this was written by someone else I do not fully understand how it works hence the reason for posting here after assistance.

I try your step -1 but thee was no difference.

 

[hyat]

But Excel_Assis, your code does not create any new records. As you can see in the example you have provided of Field Hand Services, in post 17 there is a Job A and a Job B number. What your code does is replace the Job A number with the Job B number (if it exists). So Field Hand Services with 1046 in the Job No. A column will have that 1046 replaced with 2021. Same is the case with Petty Cash, Job No. 1069 in A has been replaced with Job No. 2099 from B.

Let me point out the line that does this:

If .Range("E" & i).Value <> "" Then
.Range("E" & i).Offset(0, -1).Value = .Range("E" & i).Value
End If

In words, if the Job No. B column (i.e. column E of Sheet2) is not blank then the column immediately to the left of it, i.e. the Job No. A column (column D of Sheet2) will be set to the corresponding value of the Job No. B column.

Then after that the Job No. B column is deleted:

.Columns("E").Delete

If you don't want the Job No. A to be replaced with the Job No. B then you will have to change the condition and subsequent code highlighted above. In words, you may want to check that if Job No. B is not blank, but if Job No. A is also not blank, then create a new record by inserting a row or adding a record at the end of your list and filling it accordingly for Job No. B.

 

[Excel_Assis]

Hi Hyat thanks for the advise however do you know how to do this as I do not know how to code your suggestion.

But the macro works on the sample1 I posted.

 

[jeffreybrown]

Hi Darren,

Sorry I just can't let this one go. Thanks for the follow-up e-mail to let me know the commenting on the code you asked for in a previous thread worked.

Well actuall, by the non-exsistent e-mail my guess is it didn't do you any good and sure hope your job was saved (your words).

Anyway, not a problem if you don't properly thank folks for the efforts in trying to help you amongst others. I don't say you should go completely out of your way to thanks those who help, but some type of acknowlegement one way or the other would go a long way.

Also, just like in the last thread where some of the same advice was dispensed, the problem lies in explaining yourself accurately. Over 30 post in the previous thread (plus numerous e-mails) and 26 posts in this thread means you need to really think out your requirements so those helping can get you a fix in short order.

 

[Excel_Assis]

Jeffrey no internet access for the last 2 days been out of town, just arrived back a few hours ago and working my way through over 50 emails.

Please see your email.

I do appreciate all your help and thanks again.

The code explaination was very informative and halped me understand how it worked.