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?

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

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

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

On 8 aug, 14:40, Jim Jackson <JimJack...@discussions.microsoft.com>
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;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?- 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?

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


"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;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?
> >
> >

On 8 aug, 14:50, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
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 D35, 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;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?- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

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