mardi 11 mai 2021

Using Excel VBA I am trying to create a joint string in another workbook from my current

what im trying to do is this:

One work book has 3 columns where numbers go, and each column has the words "critical, consumable and Maintenance". Whenever there is a value in these columns (even if 2 of them have it), on another excel workbook I need to type the column names together like "Critical & Consumable"

Current Code:

Sub Sampleif()

Dim c As String
c = "Critical"

Dim d As String
d = "Consumables"

Dim e As String
e = "Routine Maintenance"

Dim x1 As ThisWorkbook
Set x1 = Workbooks("Data Collection with Macro.xlsm")


Dim y1 As Workbook
Set y1 = Workbooks("Facilitation.xlsx")

Dim r As Integer
Dim lastRow As Integer

lastRow = x1.Sheets("data").Cells(Rows.Count, 1).End(xlUp).Row
' Debug.Print (lastRow)

Dim q As String
q = 7



For r = 9 To lastRow
    If CInt(x1.Sheets("data").Cells(r, 13).Value) Or CInt(x1.Sheets("data").Cells(r, 14).Value) Or CInt(x1.Sheets("data").Cells(r, 15).Value) > 0 Then
        Debug.Print (x1.Sheets("data").Cells(r, 13).Value)
        y1.Sheets("SOS Q&A - VENDORS").Cells(q, 31).Value = c
        
        
        ElseIf CInt(x1.Sheets("data").Cells(r, 13).Value) Or CInt(x1.Sheets("data").Cells(r, 14).Value) Or CInt(x1.Sheets("data").Cells(r, 15).Value) > 0 Then
        y1.Sheets("SOS Q&A - VENDORS").Cells(q, 31).Value = d
        
        ElseIf CInt(x1.Sheets("data").Cells(r, 13).Value) Or CInt(x1.Sheets("data").Cells(r, 14).Value) Or CInt(x1.Sheets("data").Cells(r, 15).Value) > 0 Then
        y1.Sheets("SOS Q&A - VENDORS").Cells(q, 31).Value = e
        
        
        
    End If
    q = q + 3
    
Next r

End Sub

Aucun commentaire:

Enregistrer un commentaire