VBA amend code to add text!

[VBA learner ITG]

Good afternoon,

The below code only adds the worksheet name to column cell range D2-D5 regardless of what text is in cells D2-D5.

Can I get your guidance to amend the code that if there is a word "Category" or "Store" in column D that it adds the worksheet Name and _with the cell value which is either going to be "Category" "Store" instead of it only being applicable to cells D2-D5 per the below code?

e.g:

Sheet1_Category
Sheet1_Store

 ws.Range("D2").Value = ws.Name & "_" & ws.Range("D2").Value
                ws.Range("D3").Value = ws.Name & "_" & ws.Range("D3").Value
                ws.Range("D4").Value = ws.Name & "_" & ws.Range("D4").Value
                ws.Range("D5").Value = ws.Name & "_" & ws.Range("D5").Value
                DoEvents
                ws.Copy
                ActiveWorkbook.SaveAs sheetDirectory & Application.PathSeparator & ws.Name & "_" & wbName
                DoEvents
                ActiveWorkbook.Close
                validWs = True
        End If
    Next ws

 

[Joe4]

I am not quite clear what your question/issue is.

Are you saying that that there might be more cells in column D other than D2:D5 that you want to apply this to?
And are you saying that you only want to apply this to cells that have the words "Category" or "Store" in them?
Does this mean that you just want to ignore the other cells that have other values in them?

 

[VBA learner ITG]

Good afternoon,

found a solution by amending the code to the below:

categoryData = ws.Range("D1").Resize(ws.UsedRange.Rows.count, 1)
For i = 1 To UBound(categoryData)
If categoryData(i, 1) = "store" Then categoryData(i, 1) = ws.Name & "_" & categoryData(i, 1)
If categoryData(i, 1) = "category" Then categoryData(i, 1) = ws.Name & "_" & categoryData(i, 1)
Next i
ws.Range("D1").Resize(ws.UsedRange.Rows.count, 1) = categoryData
DoEvents
ws.Copy
ActiveWorkbook.SaveAs sheetDirectory & Application.PathSeparator & ws.Name & "_" & wbName
DoEvents
ActiveWorkbook.Close
validWs = True
End If
Next ws