Can't make this VB work plz assist :p

[Bokazoit]

The code below should copy the the first row in range a, if the number in the coloumn 6 in range a is greater than zero, and then paste that row into the first row in range b. If not above 0 it should do nothing...for now

I'm not so strong in vb, but under constant training hope You will help

Please Login or Register  to view this content.

In advance tx

[K Dales]

I will try to give some pointers with comments:

Dim a as Range, b as Range
' Always best to explicitly define your variables and specify type of variable

.... ' initial part of code looks OK

Set a = Range("A70:H79")
Set b = Range("A83:H92")
' Use Set when assigning an object (like a Range) to a variable
For i = 1 To 10
If a.Cells(i, 6).Value > 0 Then
' a(i,j), which is valid code for referring to an array variable, will not
work if a is a range. There are various ways to refer to cells relative to a
particular range, the above is probably easiest to put into your code. If
you want to include the first row in your range, use i instead of i+1. I
made this change since it seemed likely that was what you wanted.
For ii = 1 To 8
b(i, ii) = a(i, ii)
Next ii
' You need to close your loop for ii
Exit For ' Unclear to me why - this will end the i loop after the first
occurrence of a value above zero in column 6 was found, so only the first
occurrence will be copied; is that what you intend?
End If
Next

--
- K Dales


"Bokazoit" wrote:

>
> The code below should copy the the first row in range *a*, if the number
> in the coloumn 6 in range a is greater than zero, and then paste that
> row into the first row in range *b*. If not above 0 it should do
> nothing...for now
>
> I'm not so strong in vb, but under constant training hope You will
> help
>
>
> Code:
> --------------------
> Dim a() As Variant, b() As Variant
>
> Range("A57:H66").Select
> Selection.Copy
> Range("A70:H79").PasteSpecial (xlPasteValuesAndNumberFormats)
> Application.CutCopyMode = False
> Selection.Sort Key1:=Range("F71"), Order1:=xlDescending, Header:=xlGuess _
> , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> a = Range("A70:H79")
> b = Range("A83:H92")
> For i = 1 To 10
> If a(i + 1, 6) > 0 Then
> For ii = 1 To 8
> b(i + 1, ii) = a(i + 1, ii)
> Exit For
> End If
> Next
> End Sub
>
> --------------------
>
>
> In advance tx
>
>
> --
> Bokazoit
> ------------------------------------------------------------------------
> Bokazoit's Profile: http://www.excelforum.com/member.php...o&userid=11643
> View this thread: http://www.excelforum.com/showthread...hreadid=467802
>
>

[Bokazoit]

Were are my manners

Tx for Your help it works nicely

I'm not good at vb so I use the help file and try to set things up through that