CountA + For + If +Variable

PoorFrog

New Member
Joined
Jan 9, 2020
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
Hi everyone,

I will reformulate my request.

We have a table of hundreds columns and lines.
We are searching lines after lines.

Line 1 : using CountA we have to check if line 1 column C Not is empty or not
then jump 4 columns foward to column G to check if line 1 column G Not is empty or not
then jump 4 columns foward to column K to check if line 1 column K Not is empty or not
etc ...

If
CountA <> 1 we will return the value "00" on line 1 last column
Else
we will look up for the value and find the reference in another sheet "Data" and return it in last column

Then repeat the same process for Line 2.

My issue is that between column C, G, K etc... there are non empty columns.
The code I wrote takes in consideration all the columns and returns "00".


For my example : see file attached

I have to introduce a variable "i"
i = 1 To nb_instrument_max

then set the maximum number of instruments
nb_instrument_max = 4

then for each column have to jump from 4 to 4 for each instrument
c = (i * 4) - 1

THEN I AM LOST. I don't know how to integrate that in the code.

VBA Code:
Private Sub Composers()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim dl As Integer
    Dim pl As Range
    Dim t As String
    Dim i As Integer
    Dim nb_instr_max As Integer
    Dim c As Long
   
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("SCORE")
   
         With Sheets("SCORE")
             dl = .Cells(Application.Rows.Count, 1).End(xlUp).Row
             Set pl = .Range("R2:R" & dl)
         End With
       
       
       
         For Each cel In pl
            If Application.WorksheetFunction.CountA(cel.Offset(0, -15).Resize(1, 15)) <> 1 Then
                cel.Value = "00"
             Else
                t = cel.Offset(0, -15).Resize(1, 15).SpecialCells(xlCellTypeConstants).Value
                    cel.Value = Sheets("Data").Columns(5).Find(t, , xlValues, xlWhole).Offset(0, 1)
            End If
        Next    
               
        With Worksheets("SCORE").Range("A2")
        .Offset(RowCount, 18).Formula = "=VLOOKUP(STORE!A:A, DATA!$A$2:$J$10, 2, FALSE)"
        .Offset(RowCount, 19).Formula = "=CONCATENATE(SCORE!S:S&R:R)"
        End With
               
End Sub

Find_Match_Return-Value.gif


Thanks a lot for your help.
 
Last edited:

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Watch MrExcel Video

Forum statistics

Threads
1,114,571
Messages
5,548,843
Members
410,877
Latest member
RaeB
Top