Can't make this VB work plz assist :p
 

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

Can't make this VB work plz assist :p
 

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

Can't make this VB work plz assist :p
 

Were are my manners :rolleyes:

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 :(


--
Bokazoit
------------------------------------------------------------------------
Bokazoit's Profile: http://www.excelforum.com/member.php...o&userid=11643
View this thread: http://www.excelforum.com/showthread...hreadid=467802