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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,114,519
Messages
5,548,523
Members
410,844
Latest member
Juno49
Top