converting a simple formula into VBA code

[bartman1980]

I want to convert the formula into a VBA code, only I don;t know
excatly how to do this.
This is my formula:
=IF(A13=5;E13;IF(A12=5;E12;IF(A11=5;E11;IF(A10=5;E10;IF(A9=5;E9;IF(A8­
=5;E8;E7))))))

This is what he supposed to do:
On each row in column E is an articlegroup or articlenumber.
Next to the articlegroup is a description.
Example:
There are 3 different partnumbers with their own description, BUT they
all have a common desciption.

So basically I have to copy the description of the articlegroup next
to the desription of the articlenumber.

In column A are cells with a 5 or 6.
If there is a 5, than there is an articlegroup description which
should be copied next to the articlenumber.
If there is a 6, than he should look one cell above to look if there
is a five. And he has to repeat this when he finds the 5.

I was thinking about this one:

On Error GoTo End
For i = Sheets(1).Range("a65000").End(xlUp).Row To 1 Step -1
If Cells("A" & i - 2) = 5 Then
Cells("E" & i).Select
Selection.Copy
Cells("D" & i - 1).Select
Selection.Paste
Else
End If
Next
End:

But this doesn't work prettig much.
Can somebody help me how to handle this?

 

[Jim Jackson]

Activecell = " =IF(A13=5;E13;IF(A12=5;E12;IF(A11=5;E11;" _
&"IF(A10=5;E10;IF(A9=5;E9;IF(A8­=5;E8;E7))))))

I placed a line break to conform to the line limitations of this message.

 

[Jim Jackson]

I forgot to place " at the end of the formula. Hit "Post" too quickly.

 

[Tom Ogilvy]

Try this on a copy of your spreadsheet.

Change the 2 to row of the first cell that you want to process

Sub CopyDescriptions
Dim rng1 as Range, cell as Range
set rng1 = Range(cells(2,"A"),cells(rows.count,"A").End(xlup))
for each cell in rng1
if cell.Value = 6 then
s = cells(cell.row,"E").Value
elseif cell.Value = 5 then
cells(cell.row,"D").value = s
end if
Next
End Sub

 

[bartman1980]

I forgot to place " at the end of the formula. Hit "Post" too quickly.
--
Best wishes,

Jim











- Tekst uit oorspronkelijk bericht weergeven -

Hi Jim,
Can I fill this formula in the VBA editor as a code?

 

[Tom Ogilvy]

Think I had the 5s and 6s reversed.

Sub CopyDescriptions
Dim rng1 as Range, cell as Range
set rng1 = Range(cells(2,"A"),cells(rows.count,"A").End(xlup))
for each cell in rng1
if cell.Value = 5 then
s = cells(cell.row,"E").Value
elseif cell.Value = 6 then
cells(cell.row,"D").value = s
end if
Next
End Sub

This assumes that the description on a 5 row is in a lower number row than
the type 6 row where it needs to be copied.

row 2 5
row 3 6
row 4 6
row 5 6
row 6 5
row 7 6

so the description in E2 would be copied to D35, E6 to D7.

 

[bartman1980]

Think I had the 5s and 6s reversed.

Sub CopyDescriptions
Dim rng1 as Range, cell as Range
set rng1 = Range(cells(2,"A"),cells(rows.count,"A").End(xlup))
for each cell in rng1
if cell.Value = 5 then
s = cells(cell.row,"E").Value
elseif cell.Value = 6 then
cells(cell.row,"D").value = s
end if
Next
End Sub

This assumes that the description on a 5 row is in a lower number row than
the type 6 row where it needs to be copied.

row 2 5
row 3 6
row 4 6
row 5 6
row 6 5
row 7 6

so the description in E2 would be copied to D35, E6 to D7.

--
Regards,
Tom Ogilvy








- Tekst uit oorspronkelijk bericht weergeven -

Hi Tim,
This works perfect, thank you very much!