I have created a macro that opens and edits excel workbooks for me.

Firstly, it adds a new column A and then changes the title for A1, B1, C1, D1.

After this, I want to create some sort of formula / a loop in VBA that adds in a piece of text (31/12/2014) in this case to each cell in A but only if there is text in the adjacent cell to the right RC[1].

Does anybody have an idea as to how I should edit the indicated code of my VBA to carry this through?

Not as important but I would like that date to be changed through my excel sheet. Is there an easy way of doing that as when I did it using Range("E7").Value (and edited E7). The formatting was out in all of my other workbooks.

Sub test()

Dim MyPath          As String
Dim MyFile          As String
Dim Wkb             As Workbook
Dim Cnt             As Long

Application.ScreenUpdating = False

'MyPath = "G:\SHARED\Style Research\Portfolios - Macro Test"
MyPath = Range("D6").Value

If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

MyFile = Dir(MyPath & "*.xls")

Cnt = 0
Do While Len(MyFile) > 0
    Cnt = Cnt + 1
    Set Wkb = Workbooks.Open(MyPath & MyFile)

'this is the part I'm referring to
    Wkb.Worksheets("Sheet1").Range("A1:A250") = "31/12/2014"

    'Wkb.Worksheets("Sheet1").Range("A1").EntireColumn.NumberFormat = "DD/MM/YYYY"
    Wkb.Worksheets("Sheet1").Range("A1") = "Date"
    Wkb.Worksheets("Sheet1").Range("B1") = "Identifier"
    Wkb.Worksheets("Sheet1").Range("C1") = "Name"
    Wkb.Worksheets("Sheet1").Range("D1") = "%"

    Wkb.Close savechanges:=True
    MyFile = Dir

If Cnt > 0 Then
    MsgBox "Completed...", vbExclamation
    MsgBox "No files were found!", vbExclamation
End If

Application.ScreenUpdating = True

End Sub

This is pretty simple, use a for loop with an if condition like this -

Sub test()
For Each c In Range("A:A")
 If c <> 0 And c.Offset(, 1) <> 0 Then
     c.Value = 1
 End If
End Sub

You can change the range to fit your data, or you can try to get the usedrange. I don't quite understand your second part.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.