I have a file which is modified through VBA. It is concatenating three columns in the sheet to create a name.
However, another information needs to be concatenated to create the new data. The data needs to be created by deducing something from data in another workbook.
In a scpecific column, with always the same name (but whose location can change, however in the sheet), the macro needs to look for a specific information. There can be four possibilities.
Once this possibility is identified, once the term is matched from either of these four, the VBA should increment the number in the end of the term in the workbook needs to be incremented.
The structure of is as follows in the first workbook:
For "Noup" there are four cases : Noupx, Noupy, Noupu, Noupa
(or possibly NipNupNoupx, NipNupNoupu...)
Then the VBA should go in the other workbook, look for either the term "Noupa", "Noupu", "Noupx", "Noupy".
For each of these the specific number comming after "Noupa" (or the other) should be identified and should increment it by adding "+1".
Thus the result would be:
For the time being, I have the following VBA code, I do not know how to look for data in another workbook and increment it.
Sub TralaNome()
Const q = """"
' get source data table from sheet 1
With ThisWorkbook.Sheets(1).Cells(1, 1).CurrentRegion
' check if data exists
If .Rows.Count < 2 Or .Columns.Count < 2 Then
MsgBox "No data table"
Exit Sub
End If
' retrieve headers name and column numbers dictionary
Dim headers As Dictionary
Set headers = New Dictionary
Dim headCell
For Each headCell In .Rows(1).Cells
headers(headCell.Value) = headers.Count + 1
Next
' check mandatory headers
For Each headCell In Array(("Costumer", "ID", "Zone“, "Product Quali", "Spec A", "Spec B", "Spec_C", "Spec_D", "Spec_1", " Spec_2", " Spec_3", " Spec_4", " Spec_5", " Spec_6", " Spec_7", "Chiavetta", "Tipo_di _prodotto", "Unicorno_Cioccolato", “cacao tree“)
If Not headers.Exists(headCell) Then
MsgBox "Header '" & headCell & "' doesn't exists"
Exit Sub
End If
Next
Dim data
' retrieve table data
data = .Resize(.Rows.Count - 1).Offset(1).Value
End With
' process each row in table data
Dim result As Dictionary
Set result = New Dictionary
Dim i
For i = 1 To UBound(data, 1)
MsgBox "Empty row"
Exit For
result(result.Count) = _
q & "ID " & data(i, headers("ID ")) & _
q & " Tipo_di _prodotto " & data(i, headers("Tipo_di _prodotto")) & _
q & " cacao tree " & data(i, headers("Nupu")) & _
q
End Select
Next
' output result data to sheet 2
If result.Count = 0 Then
MsgBox "No result data for output"
Exit Sub
End If
With ThisWorkbook.Sheets(2)
.Cells.Delete
.Cells(1, 1).Resize(result.Count).Value = _
WorksheetFunction.Transpose(result.Items())
End With
MsgBox "Completed"
End Sub
The columns are grouped through this macro, but I need to now look in the other worksheet, increment the various Noupu, Noupy etc etc etc...
I think that a VBA of that sort should be used to add an incremented value :
Function GetLastRowWithData(WorksSheetNoupa As Worksheet, Optional NoupaLastCol As Long) As Long
Dim lCol, lRow, lMaxRow As Long
If NoupaLastCol = 0 Then
NoupaLastCol = wsSheet.Columns.Count
End If
lMaxRow = 0
For lCol = NoupaLastCol To 1 Step -1
lRow = wsSheet.Cells(wsSheet.Rows.Count, lCol).End(xlUp).Row
If lRow > lMaxRow Then
lMaxRow = lRow
End If
Next
GetLastRowWithData = lMaxRow
End Function
(sorry, this probably should be a comment but I don't have enough reputation as yet).
However even without checking through your code in detail, I'm seeing an exit for in the middle of a for loop without an If to avoid it in certain conditions. Presumably this means that whatever's written below that line in the loop, never gets done - nor is the loop any good for anything but the first instance. (it's the loop that's annotated 'process each row in table data)
Have you tried running this step by step? (go into the VBEditor with a test dataset open, and hit F8 or the 'step into' button in debug toolbar )