VBA - Populate cell if adjacent cell is not empty
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
Next
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.
Related videos on Youtube
Ewldh20
Updated on September 18, 2022Comments
-
Ewldh20 over 1 year
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) Wkb.Worksheets("Sheet1").Range("A1").EntireColumn.Insert 'this is the part I'm referring to Wkb.Worksheets("Sheet1").Range("A1:A250") = "31/12/2014" 'Range("D7").Value '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 Loop If Cnt > 0 Then MsgBox "Completed...", vbExclamation Else MsgBox "No files were found!", vbExclamation End If Application.ScreenUpdating = True End Sub