PDA

View Full Version : Solved: loop, find value and replace assistance



YellowLabPro
05-16-2007, 01:48 PM
In my worksheet, columns: Z, AA, AC, AI, AJ and AK are the relevant columns.

Z is cost and manually entered
AA is provides price from a formula, if the price needs attention, the term "Convert" is entered: =VLOOKUP(Z83,MarkUp!A:AI,IF(ISNA(MATCH(AE83,MarkUp!$1:$1,0)),2,MATCH(AE83,M arkUp!$1:$1,0)))
AC is qty and manually entered
AI is a item qualifier and is dynamically created by J and K
AJ is a formula =IF(AND(AA83="Convert",AI83="WL"),Z83*4,"")
AK is a formula =IF(AND(AA83="Convert",AI83="WL"),AC83/4,"")

What I am hoping to do is:
1) loop through AJ and if it finds a value, replace the formula in Z w/ the value from AJ.
2) loop through AK and if it finds a value, replace the formula in AC w/ the value from AK.

What would be even slicker is to do this all in code. But I am not there yet...

thanks,

YLP

Bob Phillips
05-16-2007, 02:15 PM
Public Sub ProcessData()
Const TEST_COLUMN As String = "Z" '<=== change to suit
Dim i As Long
Dim iLastRow As Long


With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow 'iLastRow to 1 Step -1
If .Cells(i, "AJ").Value "" Then
.Cells(i, "Z").Value = .Cells(i, "AJ").Value
End If
If .Cells(i, "AK").Value "" Then
.Cells(i, "AC").Value = .Cells(i, "AK").Value
End If
Next i

End With

End Sub

mdmackillop
05-16-2007, 02:18 PM
Good start! :clap::clap::clap:
The rest is up to you Doug.:devil2:

YellowLabPro
05-16-2007, 03:54 PM
Thanks Bob,
Huge help. This helped me see how to find a last row when doing a loop, one of the many things I have been struggling with in loops.
I replaced the "" w/ > 0. This does the trick for rows that have values, for rows that are blank it replaces the values back in Z w/ blank because the formula in AJ and AK.

If I change my formula from
=IF(AND(AA83="Convert",AI83="WL"),Z83*4,"")
to
=IF(AND(AA83="Convert",AI83="WL"),Z83*4,Z83)
then your code works.
But I am wondering how to avoid changing the original formula.
I am wondering if an Else If or Do nothing would work.
I am searching the sites for examples... have not found anything yet.

Thanks again,

Doug

I am working on whether to place values in the entire column of the worksheet or to alter the formula in the code.

Bob Phillips
05-16-2007, 04:31 PM
Doug, Am I missing something. The formula shouldn't matter, if the formula retuns "", then the test for "" should pass.

mdmackillop
05-16-2007, 04:53 PM
Hi Doug,
As LastRow is such a common requirement, you could consider creating a function to get its value. Such as
Sub Test()
Dim Chk As Long
'Simple example
MsgBox LRow(InputBox("Enter number or letter"))
'Last row on Sheet2, column 1
Chk = LRow(1, "Sheet2")
MsgBox Chk
'or as used in a range
Range(Cells(1, 1), Cells(LRow(1), 2)).Select
End Sub

Public Function LRow(Col As Variant, Optional sh As String) As Long
If IsNumeric(Col) Then Col = CLng(Col)
If Not sh = "" Then
LRow = Sheets(sh).Cells(Rows.Count, Col).End(xlUp).Row
Else
LRow = ActiveSheet.Cells(Rows.Count, Col).End(xlUp).Row
End If
End Function


Bob's code could then be written as
With ActiveSheet
For i = 1 To LRow(TEST_COLUMN)


You may prefer to pass the optional Sheet as a Worksheet, rather than a String value, if you've declared it elsewhere, in which case the function should be changed accordingly.

YellowLabPro
05-16-2007, 04:53 PM
I agree w/ you 100%.
Maybe I messed w/ your code and broke it. When I got the code, the two lines w/

If .Cells(i, "AJ").Value "" Then
If .Cells(i, "AK").Value "" Then

were in Red, and w/ MD's comment I thought you might want me to figure out what to replace, so I first put an "=" after .Value

So here is my code now:


Option Explicit
Public Sub ProcessData()
Const TEST_COLUMN As String = "Z" '<=== change to suit
Dim i As Long
Dim iLastRow As Long


With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 6 To iLastRow 'iLastRow to 1 Step -1
If .Cells(i, "AJ").Value > 0 Then
.Cells(i, "Z").Value = .Cells(i, "AJ").Value
End If
If .Cells(i, "AK").Value > 0 Then
.Cells(i, "AC").Value = .Cells(i, "AK").Value
End If
Next i

End With
Application.Calculate



End Sub

mdmackillop
05-16-2007, 04:57 PM
and w/ MD's comment
When I posted, Bob had only written two lines!

YellowLabPro
05-16-2007, 05:11 PM
If I use the following code then it blanks out all the values in Z and in AC except for the ones that have are not blank, but curiously do not copy the values from AJ and AK back into Z or AC:


Public Sub ProcessData()
Const TEST_COLUMN As String = "Z" '<=== change to suit
Dim i As Long
Dim iLastRow As Long


With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 6 To iLastRow 'iLastRow to 1 Step -1
If .Cells(i, "AJ").Value = "" Then
.Cells(i, "Z").Value = .Cells(i, "AJ").Value
End If
If .Cells(i, "AK").Value = "" Then
.Cells(i, "AC").Value = .Cells(i, "AK").Value
End If
Next i

End With
Application.Calculate



End Sub

YellowLabPro
05-16-2007, 05:13 PM
When I posted, Bob had only written two lines!

OH, I thougth for sure you guys were teaching me TOUGH LOVE!
Which I am good with, but I was scratching my head thinking that everything looked good....
But then when I experienced the problems w/ the code... you knew something and were letting me know....

YellowLabPro
05-16-2007, 05:22 PM
Bob,
So you have visual, I am uploading the workbook.

All the values have been converted to make the file size upload limits. But at least you can see what results I have.

thanks,

Doug

mdmackillop
05-16-2007, 05:33 PM
That code is copying only blank values to Columns Z and AC. If you want to copy Blanks and Values (I may be misunderstanding), then remove the If statements, but then this would be better done with Copy and Paste to save the looping.
For i = 6 To iLastRow 'iLastRow to 1 Step -1
.Cells(i, "Z").Value = .Cells(i, "AJ").Value
.Cells(i, "AC").Value = .Cells(i, "AK").Value
Next i

YellowLabPro
05-16-2007, 05:41 PM
MD,
What I am hoping to do is copy only values that are not blank. In Bob's OG code he did not have an "=" sign inbetween the .Value and "Then", so did I mess up his code by placing it in there?
If I don't have some sort of operator, then the code appears in Red.

mdmackillop
05-16-2007, 05:48 PM
In that case
If .Cells(i, "AJ").Value <> "" Then
.Cells(i, "Z").Value = .Cells(i, "AJ").Value
End If
If .Cells(i, "AK").Value <> "" Then
.Cells(i, "AC").Value = .Cells(i, "AK").Value
End If

mdmackillop
05-16-2007, 05:50 PM
BTW, how many rows are you working with? I know you have some very big sheets.

YellowLabPro
05-16-2007, 06:14 PM
Sorry for the delayed response, the email notifications are not coming through....

This is not a big sheet, anywhere from 20-500, but typically about 100.

YellowLabPro
05-16-2007, 06:40 PM
MD,
That did the trick, <>, thanks.

Bob,
Thank you very much for providing the entire code, sorry I missed the simple point on the operator.

I have some follow-up questions on how/why you did a couple of things, but will hit you up tomorrow.

Again, much appreciated and a big thanks,

Doug

YellowLabPro
05-17-2007, 07:09 AM
Bob,
When you can find a spare moment, would you provide some follow-up info on your program?
1) You declared this a as Public procedure, why and benefit to this?
2) Constant, I have not used this method previously. The same sort of questions here, why you did it this way and the benefits. It works great, curious for future methods.

I think that covers it for now,

Thank you,

YLP

Bob Phillips
05-17-2007, 08:20 AM
1) I always declare my procedures, either Public or Private, part of my aim to leave no ambiguity in my code. If my code is to be accessed from outside the module it is contained within, I declare it Public, if not I declare it Private. I also add Option Private Module to the head of my modules, unless I want to expose the Public procedures outside of the project they are in.

2) Const is just a way to pre-load a variable that will not change again in the code. I find it a convenient way hold set values, and by declaring it as a constant, it is defined at the front of the code, making it obvious for anyone who wants to change it.

In reality, I rarely use this second method in my real work code, as I tend to create an application class and load all of my constants in there, and access them via the class interface.

Bob Phillips
05-17-2007, 08:22 AM
PS, Public is the default for procedures, but like I said, I prefer to remove any possible ambiguity. I also tend to try and declare proceure arguments explicitly as ByRef or ByVal, not leave it to the default (ByRef).