PDA

View Full Version : [SOLVED] Error 1004



mand7841
01-30-2017, 11:07 AM
I am trying to create a column "H" that will display the values in column F unless F is empty. In such a case, the value from column E will be entered into column H, however I am new to using vba and am not sure how to fix this macro as it continues to fail on the first line "If Range("Fi").Value = Empty Then".
Any advice would be greatly appreciated!


Sub AdditionalColumn()
Dim i As Integer
i = 2
Do:
If Range("Fi").Value = Empty Then
Range("Hi").Value = Range("Ei").Value
Else: Range("Hi").Value = Range("Fi").Value
End If
i = i + 1
Loop
End Sub

mancubus
01-30-2017, 02:25 PM
welcome to the forum.
pls use code tags when posting your code. see my signature.


Range("F" & i), Range("H" & i), etc

mand7841
01-30-2017, 02:35 PM
Thank you for your reply!
I tried what you suggested and now I am getting an error that says:
"Run-time error '424':
Object required"

mand7841
01-30-2017, 02:36 PM
Sub AdditionalColumn()
Dim i As Integer
i = 2
Do:
If Range("F" & i).Value.Select = Empty Then
Range("H" & i).Value = Range("E" & i).Value
Else: Range("H" & i).Value = Range("F" & i).Value
End If
i = i + 1
Loop
End Sub

burgDD
01-31-2017, 12:13 PM
This issue may occur if either of the following conditions is true:
The Microsoft Visual Basic for Applications (VBA) macro copies and pastes one whole row in an Excel 2003 workbook.
The Microsoft VBA macro copies and pastes a range of 2,516 rows or more rows in an Excel 2003 workbook.

mancubus
01-31-2017, 11:44 PM
why did you insert Select after Value?
i strongly recommend you take some trainig on vba before using it. check vbaexpress training pages or other web stuff.
that loop takes you to the bottom of the sheet and errors out at that point. i did'nt notice it at first.



Sub AdditionalColumn()
Dim i As Long
For i = 2 To Range("F" & Rows.Count).End(xlUp).Row
If Range("F" & i).Value = "" Then
Range("H" & i).Value = Range("E" & i).Value
Else
Range("H" & i).Value = Range("F" & i).Value
End If
Next i
End Sub

mand7841
02-01-2017, 06:59 AM
Thank you so much, I've asked this question on other forums and I have received many replies that have completely changed my original coding, but your changes all make sense to me. I am in the midst of training in vba but do not have a support system which is why I am asking online forums for assistance.
I created this macro to have a relative reference, but because the output value is "H", the output is always in that column. What could I set "H" equal to so that the output would be relative?

Thank you!

mancubus
02-01-2017, 11:00 PM
glad to hear about vba training.

can you elaborate the relative reference?
relative to what?

mancubus
02-01-2017, 11:11 PM
Cells(Row Index, Column Index)

Column H is 8th column. when you refer to a cell in Column H its column index is 8


using another variable for column reference



Sub AdditionalColumn()
Dim i As Long, j As Long
j = 8
For i = 2 To Cells(Rows.Count, j - 2).End(xlUp).Row
If Cells(i, j - 2).Value = "" Then
Cells(i, j).Value = Cells(i, j - 3).Value
Else
Cells(i, j).Value = Cells(i, j - 2).Value
End If
Next i
End Sub


j = 8 means Column is H in this case.
j - 2 is Column F
j - 3 is Column E


for Column N (instead of column H) for instance, change j = 8 to j = 14

j = 11 means Column is N
j - 2 is Column L
j - 3 is Column K

each iteration of i increments the row index by 1 in Cells(i, j).

mand7841
02-02-2017, 01:49 PM
Thank you! What you supplied did not quite work, but I adjusted it to what I need. Thank you for all of your assistance!

mancubus
02-02-2017, 03:15 PM
post your solution in case someone will need it in the future and mark the thread solved from threadtools please.

mand7841
02-03-2017, 06:32 AM
Sub Relative_Additional_Column()
Dim i As Long
Dim j As Long
j = 11
For i = 2 To Cells(Rows.Count, j - 2).End(xlUp).Row
If Cells(i, j - 1).Value = Empty Then
Cells(i, j).Value = Cells(i, j - 2).Value
Else
Cells(i, j).Value = Cells(i, j - 1).Value
End If
Next i
End Sub


j has to be set to whatever column chosen, and the two other columns are simply subtract or add how many columns away they are, relative to j.