Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't make this VB work plz assist :p
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 :( -- Bokazoit ------------------------------------------------------------------------ Bokazoit's Profile: http://www.excelforum.com/member.php...o&userid=11643 View this thread: http://www.excelforum.com/showthread...hreadid=467802 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make this work? | Excel Discussion (Misc queries) | |||
Make table query will work in datasheet view but will not make tab | Excel Discussion (Misc queries) | |||
Cant make it work | New Users to Excel | |||
can't make it work, I need help | Excel Worksheet Functions | |||
Spin button in a work sheet - how do I make it work? | Excel Worksheet Functions |