converting a simple formula into VBA code
 

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;E 10;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?

converting a simple formula into VBA code
 

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.
--
Best wishes,

Jim


"bartman1980" wrote:

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;E 10;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?

converting a simple formula into VBA code
 

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

Jim


"bartman1980" wrote:

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;E 10;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?

converting a simple formula into VBA code
 

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

--
Regards,
Tom Ogilvy






"bartman1980" wrote:

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;E 10;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?

converting a simple formula into VBA code
 

On 8 aug, 14:40, Jim Jackson
wrote:
I forgot to place " at the end of the formula. Hit "Post" too quickly.
--
Best wishes,

Jim



"bartman1980" wrote:
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;E 10;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?- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

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

converting a simple formula into VBA code
 

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 D3:D5, E6 to D7.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy






"bartman1980" wrote:

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;E 10;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?

converting a simple formula into VBA code
 

On 8 aug, 14:50, Tom Ogilvy
wrote:
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 D3:D5, E6 to D7.

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote:
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

--
Regards,
Tom Ogilvy

"bartman1980" wrote:

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;E 10;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?- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

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