PDA

View Full Version : Compare cell values & do an autofill



khaos
09-28-2010, 08:16 PM
Hey guys .. I'm stuck on a peculiar problem & I don't even know where to start, well I don't even know if it's possible.

I am basically trying to write a macro that compares cell values & based on those cell values the other cell will be autofilled. I basically have price data for different months & this data is dynamic so I can't even do it based on rows since I would be using this macro as a standard among different workbooks.

It basically would be comparing data across 3 cells. One is the Month .. Two is the Item in that month & the third would be to see if there is an amount in the third column, if all these values are true it would do an autofill in the empty cell below the amount(whatever that amount may be). I know it is perhaps an If-Then Statement but I would be doing this for specific columns. So I would have to define that as well.

I hope this isn't too confusing but I'm attaching a sample piece of my workbook to give you guys a picture of what I'm talking about. I need an autofill in the Items & Amount Column based on values from the Month Column. So basically if there is January & there is an Item, the Amount of that would be the same based on the cell above it & it should run only until the month of January & if there is no Item in February, it should be blank for the Amounts coinciding with February. Same with the Items Column I need an autofill in it depending on the Months column, If there is 1 Item and 3 values of January then I want the autofill for 3 cell values(In the Items Column) until it finds the next value and tries to do the same autofill for the next month (February).
I'm just confused talking about it, but hopefully the attachment can give you an idea of what I'm trying to get at. Without even too much scripting can someone write a =IF statement within a cell that may solve this problem & I can get it hardcoded through the Macro recorder.

Any comments on this would be highly appreciated.

Thanks!

Kenneth Hobs
09-29-2010, 10:16 AM
You can use an IF() formula. Another method is to use a worksheet change event.

If you attach a short xls, it is easier to help.

khaos
09-29-2010, 10:56 AM
Thanks for the response,

Here you go.

Kenneth Hobs
09-29-2010, 11:26 AM
You could have used two simple IF formulas for the columns C and E and overwrite them with the data. It would then autofill.

For a macro method, this should work. However, in your data, some entries in column have extra space characters. You should use data validation to make sure that proper data is entered.

Only test with backup data.
Sub FillCE()
Dim tRows As Long, i As Long
tRows = ActiveSheet.UsedRange.Rows.Count
For i = 3 To tRows
If Range("C" & i).Value = "" Then
If Range("A" & i).Value = Range("A" & i - 1).Value Then _
Range("C" & i).Value = Range("C" & i - 1).Value
End If
If Range("E" & i).Value = "" Then
If Range("A" & i).Value = Range("A" & i - 1).Value Then _
Range("E" & i).Value = Range("E" & i - 1).Value
End If
Next i
End Sub

The third method is the worksheet change event method.

khaos
09-29-2010, 04:06 PM
Hey Kenneth I tried your code, it only partly works.

Only some of the Items get autofiltered to the cells below them whereas some don't. Same is the case with the Amounts. I have the spaces in there between the Months because if there aren't any months, there will definitely be no Items, so I don't want the amount to be autofiltered all the way through the column where there are no month & items. Your code didnt work for the Items "DFC22" & "DFC33". Sometimes the same Item gets carried over to other months so that's also why there are spaces in the Amounts and the code isn't catching it. Basically the finished file should look like the attachment.

Thanks for the help so far

Kenneth Hobs
09-29-2010, 04:32 PM
Please reread what I said:

However, in your data, some entries in column have extra space characters.

This means that "January" is not equal to "January ".

khaos
09-29-2010, 04:41 PM
Please reread what I said:


This means that "January" is not equal to "January ".

I don't understand what this means. It looks the same to me, I know that there is a difference between the quotations in terms of spaces. But how do I counter this?

Kenneth Hobs
09-29-2010, 04:48 PM
You need to force the user to enter the correct information with data validation or correct it yourself. In the code, you can use Trim() to trim the values for comparison if you want to skip correcting the data.

Based on what you said it should look like, I will have to examine it more. Since you don't have a value for a new item in a month and the rule is that you want to fill if it is the same month and item now, more logic in the code is needed to catch that scenario.

khaos
09-29-2010, 04:55 PM
You need to force the user to enter the correct information with data validation or correct it yourself. In the code, you can use Trim() to trim the values for comparison if you want to skip correcting the data.

Based on what you said it should look like, I will have to examine it more. Since you don't have a value for a new item in a month and the rule is that you want to fill if it is the same month and item now, more logic in the code is needed to catch that scenario.

The user won't be entering any data, I am just trying to format an existing data set the right way. I am really lost with the trim scenario since I never used it, but there are different Items in the month of January. There is DFC11 and DFC22, that's what I mean there might be more items in a month or they items may stay the same. Hence I need an autofill that catches this scenario.

Kenneth Hobs
09-29-2010, 05:10 PM
Here is the Trim() method to address problems with trailing space characters.

Sub FillCE()
Dim tRows As Long, i As Long
tRows = ActiveSheet.UsedRange.Rows.Count
For i = 3 To tRows
If Range("C" & i).Value = "" Then
If Trim(Range("A" & i).Value) = Trim(Range("A" & i - 1).Value) Then _
Range("C" & i).Value = Range("C" & i - 1).Value
End If
If Range("E" & i).Value = "" Then
If Range("C" & i).Value = Range("C" & i - 1).Value Then _
Range("E" & i).Value = Range("E" & i - 1).Value
End If
Next i
End Sub
To handle your scenario where in the same month an amount is not listed for the new item, the amount is set to the previous amount for the previous item.
Sub FillCE()
Dim tRows As Long, i As Long
tRows = ActiveSheet.UsedRange.Rows.Count
For i = 3 To tRows
If Range("C" & i).Value = "" Then
If Trim(Range("A" & i).Value) = Trim(Range("A" & i - 1).Value) Then _
Range("C" & i).Value = Range("C" & i - 1).Value
End If
If Range("E" & i).Value = "" Then
If Range("C" & i).Value = Range("C" & i - 1).Value Then _
Range("E" & i).Value = Range("E" & i - 1).Value
End If
If Range("E" & i).Value = "" Then
If Trim(Range("A" & i).Value) = Trim(Range("A" & i - 1).Value) Then _
Range("E" & i).Value = Range("E" & i - 1).Value
End If
Next i
End Sub

khaos
09-29-2010, 05:26 PM
Nice, you are awesome, the code completely did what I wanted it to do & I didn't even know if it was possible. Here's my problem though my workbook has these columns all over & each time I need to specify it to a similar Months column,Items Column & do an autofill on the Items & and Amount Column. I have the Autofill on the Months column covered since this doesn't have to be dynamic. But I need to use this autofill on the Items Column if there is a value in the Items column for that month, then to autofill that column until it encouters another value in the same column & it should only be for that particular month. The same thing for the amounts column, the code would do the same thing your code does but just specifying it do over different columns. Is this possible ?

I'm attaching the workbook for your reference.

the code for the months autofill I have covered by doing.

Sub Autofill()
'
' Autofill Columns


Dim Cell As Range

For Each cll In Range("H4:H18").Cells
If Len(cll.Value) = 0 Then cll.Offset(-1).Copy cll
Next cll

End Sub

Kenneth Hobs
09-29-2010, 05:56 PM
Sub FillMyCols()
FillCE "A", "C", "E"
FillCE "G", "I", "K"
End Sub


Sub FillCE(colMonth As String, colItem As String, colAmount As String)
Dim tRows As Long, i As Long
tRows = ActiveSheet.UsedRange.Rows.Count
For i = 3 To tRows
If Range(colItem & i).Value = "" Then
If Trim(Range(colMonth & i).Value) = Trim(Range(colMonth & i - 1).Value) Then _
Range(colItem & i).Value = Range(colItem & i - 1).Value
End If
If Range(colAmount & i).Value = "" Then
If Range(colItem & i).Value = Range(colItem & i - 1).Value Then _
Range(colAmount & i).Value = Range(colAmount & i - 1).Value
End If
If Range(colAmount & i).Value = "" Then
If Trim(Range(colMonth & i).Value) = Trim(Range(colMonth & i - 1).Value) Then _
Range(colAmount & i).Value = Range(colAmount & i - 1).Value
End If
Next i
End Sub

khaos
09-29-2010, 06:29 PM
I didn't try this code because I just looked back at my worksheet & I think I'm confusing this more than I should be. I think there is a simpler way to do this.

There is only 1 Month column that is autofilled with the code I gave you earlier & all the Items & Price columns throughout the worksheet should be linked to this Month column. which is column A

I'll list the columns I need to code, seperating the Item & Price, as there can only be a price if there is an Item, I'll list them by the ones that are linked together. The Item & Price are both linked to the Month Column ( Column A)

Columns:
R (Item), T(Price)
U (Item), W(Price)
X(Item), Z(Price)
AA(Item), AC(Price)
AD(Item), AF(Price)
AG(Item), AI (Price)
AJ (Item), AL (Price)
AM (Item),AO (Price)
AP (Item), AR (Price)
AS(Item), AU (Price)
AV(Item), AX(Price)
AY(Item), BA (Price)

BB (Item) - I need the Items and they don't have a price so I just need an autofil that links this column to the Month column (Column A)

BE(Item), BG (Price)

BH (Item) - I need the Items and they don't have a price so I just need an autofil that links this column to the Month column (Column A)

If you link three of the Items and Prices to the Months column I'll have an idea of how to do the rest. This is the final component that I'm stuck on.

Hope you can help me out. Thanks a ton!

khaos
09-29-2010, 07:29 PM
Sub FillMyCols()
FillCE "A", "C", "E"
FillCE "G", "I", "K"
End Sub


Sub FillCE(colMonth As String, colItem As String, colAmount As String)
Dim tRows As Long, i As Long
tRows = ActiveSheet.UsedRange.Rows.Count
For i = 3 To tRows
If Range(colItem & i).Value = "" Then
If Trim(Range(colMonth & i).Value) = Trim(Range(colMonth & i - 1).Value) Then _
Range(colItem & i).Value = Range(colItem & i - 1).Value
End If
If Range(colAmount & i).Value = "" Then
If Range(colItem & i).Value = Range(colItem & i - 1).Value Then _
Range(colAmount & i).Value = Range(colAmount & i - 1).Value
End If
If Range(colAmount & i).Value = "" Then
If Trim(Range(colMonth & i).Value) = Trim(Range(colMonth & i - 1).Value) Then _
Range(colAmount & i).Value = Range(colAmount & i - 1).Value
End If
Next i
End Sub

I tried your code, it fills the columns until E on the Test excel sheet, but not it doesn't perform any autofill after that.

khaos
10-03-2010, 09:36 AM
Is there any other solution ? can I get some help on this since I'm stuck on it right now. I hope you guys can help me out

-Thanks

Kenneth Hobs
10-03-2010, 07:53 PM
Your layout of the spreadsheet does not follow standard practice. Failure to fill the data to a logical degree makes logical solutions near impossible.

There are lots of ways that this can fail.
Sub FillMyCols()
FillCE "A", "C", "E"
FillCE "H", "J", "L"
End Sub


Sub FillCE(colMonth As String, colItem As String, colAmount As String)
Dim tRows As Long, i As Long
tRows = ActiveSheet.UsedRange.Rows.Count
For i = 2 To tRows
'Fill amount if needed.
If Range(colAmount & i).Value = "" Then Range(colAmount & i).Value = Range("E" & i).Value
'Fill month value if no value but item or amount <> "".
If Trim(Range(colMonth & i).Value) = "" And Columns.Count <> _
WorksheetFunction.CountBlank(Range(i & ":" & i)) Then
Range(colMonth & i).Value = Range(colMonth & i - 1).Value
End If
If Range(colItem & i).Value = "" Then
If Trim(Range(colMonth & i).Value) = Trim(Range(colMonth & i - 1).Value) Then _
Range(colItem & i).Value = Range(colItem & i - 1).Value
End If
If Range(colAmount & i).Value = "" Then
If Range(colItem & i).Value = Range(colItem & i - 1).Value Then _
Range(colAmount & i).Value = Range(colAmount & i - 1).Value
End If
If Range(colAmount & i).Value = "" Then
If Trim(Range(colMonth & i).Value) = Trim(Range(colMonth & i - 1).Value) Then _
Range(colAmount & i).Value = Range(colAmount & i - 1).Value
End If
Next i
End Sub