Replacing "IF" or "Lookup" formulas with VBA function

[dhunter43]

In worksheet1 I have a range (a1:b12).
In worksheet2 I have 12 column headings (a1:l1) and I want to search
worksheet1 range a1:a12 for the corresponding column headings then populate
worksheet2(a2:l2) with the data from worksheet1 (b1:b12).
I was able to use the "IF" formula in worksheet2 until I expanded past the 7
nested functions rule. I've tried to use Lookup and Vlookup formulas but both
require sorting which is not an option.
I've also tried to use the IFELSE function and LOOKUP functions in VBA
without success.

I appreciate the assistance and enjoy the learnings that go into problem
solving.

Thanks,
dhunter43
The Performance Advantage, LLC

 

[JLGWhiz]

Try this in the standard VBA module.

Sub cpy()
Set Sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
i = 1
For Each c In sh2.Range("$A$1:$L$1")
If c = Sh1.Cells(i, 1) Then
Sh1.Cells(i, 1).Offset(0, 1).Copy c.Offset(1, 0)
End If
i = i + 1
Next
End Sub

 

[dhunter43]

I'm getting a "Type Mismatch" error. Perhaps you need more info.
The column headings in worksheet2 text headings i.e. 14Sheeting, 15Printing
Of course I'm presuming the "Type Mismatch" refers to i = 1 as defining the
data as an integer. Otherwise I've not seen this error before.

 

[JLGWhiz]

It should have worked as long as there were no blank cells in the range.
Try this modified version and let me know if you still get the error.

Sub cpy()
Set Sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
i = 1
For Each c In sh2.Range("$A$1:$L$1")
If Not c Is Nothing Then
If c = Sh1.Cells(i, 1) Then
Sh1.Cells(i, 1).Offset(0, 1).Copy c.Offset(1, 0)
End If
End If
i = i + 1
Next

 

[JLGWhiz]

Just in case, this version defines i a little better.

Sub cpy()
Dim i As Long
Set Sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
i = 1
For Each c In sh2.Range("$A$1:$L$1")
If Not c Is Nothing Then
If c = Sh1.Cells(i, 1) Then
Sh1.Cells(i, 1).Offset(0, 1).Copy c.Offset(1, 0)
End If
End If
i = i + 1
Next
End Sub

 

[dhunter43]

still having the same problem. I was probably too vague in my initial
request. Here are the specifics with actual worksheets and cell ranges
identified. Apologies if this caused the problem.

In my actual workbook 'worksheet1' is named InputForm and 'worksheet2 is
named Formulas.
Cells B14:b25 in the InputForm worksheet are drop down lists that deliver a
value in the corresponding cells c14:c25.
Cells M1:AC1 in the Formulas worksheet are the same as the drop down list in
the InputForm worksheet. I need to populate cells M2:AC2 with the data from
InputForm C14:c25 based on user selection.
90% of the time there will be several cells left blank in InputForm b14:c25
and I would like to return a "0" in cells Formulas("M2:AC2") if an operation
isn't selceted.

Thank you,
dhunter43
The Performance Advantage, LLC

 

[JLGWhiz]

That explains the "type mismatch" error. I suggest you repost
with the following portion of your last posting. I would have to
do some research to help you here, but there are those who
are wiser than me that can help if you re-post.

In my actual workbook 'worksheet1' is named InputForm and 'worksheet2 is
named Formulas.
Cells B14:b25 in the InputForm worksheet are drop down lists that deliver a
value in the corresponding cells c14:c25.
Cells M1:AC1 in the Formulas worksheet are the same as the drop down list in
the InputForm worksheet. I need to populate cells M2:AC2 with the data from
InputForm C14:c25 based on user selection.
90% of the time there will be several cells left blank in InputForm b14:c25
and I would like to return a "0" in cells Formulas("M2:AC2") if an operation
isn't selceted.