Need to populate two columns based on content of adjacent two columns. 2

[Edward_Low]

Hi Forum,

I hope this isn't too much to ask. I am trying to find a formula or VBA for the following:

There are four columns 1000s+ deep - A, B, C and D.

C may or may not contain a value. D contains 0 or 1. The formula needs to enter data into A and B, depending on what is in C and D.

There are four possible scenarios and their responses are:


1: Nothing is entered in C and there is a '1' found in column D. - RESPONSE: Enter 'orange' in column A.
2: Nothing is entered in C and there are only zeros in column D. - RESPONSE: Enter 'plum' in column A.
3: Something is entered in C and there is a '1' found in column D. - RESPONSE: Enter 'apple' in column A.
4: Something is entered in C and there are only zeros in column D. - RESPONSE: Enter 'raisin' in column A, and 'cherry' in column B.


There are no blank rows between any section. Any one of the four possible scenarios may exist in any section.

Thanks in advance for any help.

http://files.engineering.com/getfil...d108f4&file=MACRO_TO_MATCH_COLUMNS_101116.xls

http://files.engineering.com/getfil...a0&file=MACRO_TO_MATCH_COLUMNS_101116.xls.jpg

 

[SkipVought]

Hi,

This would be a spreadsheet solution...
[tt]
A2: =IF(ISBLANK($D3),IF(E3=1,"orange","plum"),IF($E3=1,"apple","rasin"))
B2: =IF(AND(NOT(ISBLANK($D3)),$E3=0),"cherry","")
[/tt]

This would be a VBA spreadsheet solution...

Function EdLow(Sector3 As Variant, iDecimal As Integer, Col As Range)
    If Sector3 = "" Then
        If iDecimal = 1 Then
            If Col.Column = 1 Then
                EdLow = "orange"
            Else
                EdLow = ""
            End If
        Else
            If Col.Column = 1 Then
                EdLow = "plum"
            Else
                EdLow = ""
            End If
        End If
    Else
        If iDecimal = 1 Then
            If Col.Column = 1 Then
                EdLow = "apple"
            Else
                EdLow = ""
            End If
        Else
            If Col.Column = 1 Then
                EdLow = "rasin"
            Else
                EdLow = "cherry"
            End If
        End If
    End If
End Function

...where the function on the sheet would be...
[tt]
A2: =EdLow($D3,$E3,A$2)
[/tt]
...and copy/paste to B2 then copy/paste down.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

Thank you Skip. It does not work though. Possibly I'm entering the formula wrong - I'm not sure. The first formula returns 'raisin', the second returns 'cherry' - no matter which cell I enter it into.


Creating a new macro with the VBA gives the error 'Complie error: Expected End Sub'.

I labeled it raisinmacro.

I probably made a mess of it:


Sub raisinmacro()
'
' raisinmacro Macro
' Macro recorded 11/7/2016 by K
'
Function EdLow(Sector3 As Variant, iDecimal As Integer, Col As Range)
If Sector3 = "" Then
If iDecimal = 1 Then
If Col.Column = 1 Then
EdLow = "orange"
Else
EdLow = ""
End If
Else
If Col.Column = 1 Then
EdLow = "plum"
Else
EdLow = ""
End If
End If
Else
If iDecimal = 1 Then
If Col.Column = 1 Then
EdLow = "apple"
Else
EdLow = ""
End If
Else
If Col.Column = 1 Then
EdLow = "rasin"
Else
EdLow = "cherry"
End If
End If
End If
End Function Application.Goto Reference:="raisinmacro"
Application.Run "Book10!raisinmacro"
Application.Run "Book10!raisinmacro"
Application.Run "Book10!raisinmacro"
Range("B1").Select
ActiveCell.FormulaR1C1 = "A2: =EdLow($D3,$E3,A$2)"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B2")
Range("B1:B2").Select
Selection.ClearContents
Range("J34").Select

 

[SkipVought]

I am totally confused by your reply.

Here is my test table with the results using my formula...
[pre]
SECTOR-1 SECTOR-2 SECTOR-3 DECIMAL
orange 1
plum 0
apple J510M240001 1
rasin cherry 5UH46302E 0
[/pre]

I was actually perplexed by your test table. You table is not a typical table with one row of headings followed by data and nothing else. You have tables stacked on tables. What is the business case for such a structure? It is very unorthodox and does not lend itself to the data analysis features that Excel employs. The sections are VISIBLY separated, but your data structure cannot discriminate between one section and the next, unless you add a helper column to discriminate.

And the other thing that you did was to put your own EXCEL ROW and EXCEL COLUMN in the sheet, which I just realized, which throws everything off! Is that how you have your actual sheet structured; with column A having 1 in row 2 and row 1 having A in column B?

Please give me some confirmation of what your intent is for this sheet.

Give me a few minutes to work all this total confusion out.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

1) I DELETED row 1 and column A in your workbook

2) I added a column named Section with the following formula...
[tt]
E2: =IF(D2="DECIMAL",1,0)+IF(ISNUMBER(E1),E1,0)
[/tt]

3) The formulas...
[tt]
A2: =IF(ISBLANK($C2),
IF(ISNA(MATCH(1,OFFSET($D$1,MATCH($E2,Section,0),0,COUNTIF(Section,$E2),1),0)),"plum","orange"),
IF(ISNA(MATCH(1,OFFSET($D$1,MATCH($E2,Section,0),0,COUNTIF(Section,$E2),1),0)),"rasin","apple"))

B2: =IF(NOT(ISBLANK($C2)),
IF(ISNA(MATCH(1,OFFSET($D$1,MATCH($E2,Section,0),0,COUNTIF(Section,$E2),1),0)),"cherry",""),"")
[/tt]




Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's your workbook with the results.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...and here's the VBA function, again with row 1 and column A deleted from your example workbook. This function needs no helper column.

Function EdLow(Sector3 As Variant, rDec As Range, Col As Range)
    Dim lRow As Long, bFound1 As Boolean
    
    Application.Volatile
    
    bFound1 = False
    lRow = rDec.Row
    
    Do While lRow <= ActiveSheet.UsedRange.Rows.Count
        Select Case Cells(lRow, rDec.Column).Value
            Case 1
                bFound1 = True
                Exit Do
            Case "DECIMAL"
                Exit Do
        End Select
        lRow = lRow + 1
    Loop

    If Sector3 = "" Then
        If bFound1 Then
            If Col.Column = 1 Then
                EdLow = "orange"
            Else
                EdLow = ""
            End If
        Else
            If Col.Column = 1 Then
                EdLow = "plum"
            Else
                EdLow = ""
            End If
        End If
    Else
        If bFound1 Then
            If Col.Column = 1 Then
                EdLow = "apple"
            Else
                EdLow = ""
            End If
        Else
            If Col.Column = 1 Then
                EdLow = "rasin"
            Else
                EdLow = "cherry"
            End If
        End If
    End If
End Function

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

Thank you again for your time in responding to my post. Due to time considerations, I am having a programmer look at it. I greatly appreciate your help.

 

[SkipVought]

Here's your workbook with the VBA function and function used on sheet.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MiggyD]

As far as I know, you cannot have a Function inside a Sub. Even though a module may iterate itself to completion (or get stuck in an infinite loop and crash). I have never seen it done as in the manner you have posted....

I labeled it raisinmacro.
I probably made a mess of it:


[red]Sub raisinmacro()[/red]
'
' raisinmacro Macro
' Macro recorded 11/7/2016 by K
'
[red]Function EdLow(Sector3 As Variant, iDecimal As Integer, Col As Range)[/red]
If Sector3 = "" Then
If iDecimal = 1 Then
(...)
End If
[red]End Function[/red] Application.Goto Reference:="raisinmacro"
Application.Run "Book10!raisinmacro"

Please note a Function can be called from a Submodular programming section; but not from inside itself. (I don't know if you'll understand what I'm saying but I can only hope you can to some degree.)

You should paste the Function EdLow coding in the GENERAL section (aka: all by itself) and NOT INSIDE your Sub raisinmacro section of your VBA editor.

Then attempt running it again.

Skip's code is solid. I just think you didn't know "where" or possibly "how" to place it.

HTH
--MiggyD


"If a tree falls in the forest and no one is around, does it make a sound?" I finally got the answer!

 

[SkipVought]

True. You can CALL a Function within a Sub.

But you can also use a Function directly in a sheet, just as you would use a spreadsheet formula, which is how I designed this Function, as you can see from my uploaded workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

Thanks again Skip, and thank you Miggy for the reply. I believe you are right - I am not placing the code correctly. (I'm using Excel 2003. I'm not sure if this is a factor.)

I create a new macro labeled raisin2. The page reads:

Sub raisin2()

End Sub
​

Presumably, I enter the code from above (first reply to post) into the Sub space? This does not work. What am I doing wrong?

 

[SkipVought]

Why why why are you creating a Sub?

Can't you see my EdLow function, coded in a module, and used directly on the sheet? I uploaded your workbook with everything working as specified.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

Sorry.... I don't know. I have never created a macro before. Felt is safe to assume - go to Tools/Create New Macro etc.... enter the macro...

Yes, I see 'EdLow', but what do I do with the code? Enter it directly on the sheet?

 

[SkipVought]

Did you download the workbook I uploaded?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

I did. Thank you. I have it.

 

[SkipVought]

alt+F11 toggles between the sheet and the VBA Editor.

Do you see the Module containing the EdLow function?

Do you see in A2 & B2 for instance, how the EdLow function is used?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

Yes, I see how Alt/F11 toggles to the editor. It contains:

Sub raisin2()

End Sub


I do not see the EdLow module anywhere.


I do see, where B2 contains:
=IF(NOT(ISBLANK($C2)),IF(ISNA(MATCH(1,OFFSET($D$1,MATCH($E2,Section,0),0,COUNTIF(Section,$E2),1),0)),"cherry",""),"")

..and B3 contains:

=IF(AND(NOT(ISBLANK($C3)),$D3=0),"cherry","")

 

[SkipVought]

Look at the upload on 8 Nov 16 21:39.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Edward_Low]

I'm looking at it. I see where it has formulas in A and B, underneath the SECTOR-1 and SECTOR-2 headers, for example:

=EdLow($C2,$D2,B$1)