Macro help

[Jen_T]

I have a macro I created that looks at 4 columns that contain text in each,
as follows:
DK - A
DL - SK
DM - CU
DN - SPEC

In column DP, the macro should indicate "multi" if two or more are in
columns DK: DN. If only one is in all four than place the text, either "A",
"SK", "CU" OR "SPEC"

My macro is currently placing "multi" if one is indicated and if two or more
it is concenating the four columns in DN.. Example: ACU or CUSPEC OR SKCUSPEC

Can someone look at this macro and let me now what is wrong? Thank you


Select Case WorksheetFunction.CountBlank(Range("DK" & X & "N" & X))
Case 2
Cells(X, 120) = Cells(X, 115) & Cells(X, 116) & Cells(X, 117) &
Cells(X, 118)

Case Else
Cells(X, 120) = "Multi"
End Select
On Error Resume Next

 

[Mike H]

Hi,

You don't appear to checking what values are in your cells just that there
are values so how about this approach

If WorksheetFunction.CountA(Range("DK" & x & "N" & x)) > 1 Then
Cells(x, 120) = "Multi"
Else
Cells(x, 120) = WorksheetFunction.Lookup(WorksheetFunction.Rept("z",
99), Range("DK" & x & "N" & x))
End If

Mike