| View previous topic :: View next topic |
| Author |
Message |
CRL7
General User
Joined: 12 Aug 2008
Posts: 24
|
 Posted: Thu Sep 18, 2008 3:48 am Post subject: [Solved] Rows within 2 sheets, finding match and updating |
 |
|
Hello all
I have very limited knowledge of macros, if any at all, and I need to do following..
I have OO Calc file with 2 sheets: TempSheet and SKTSheet. I’d need macro to get one by one rows from TempSheet, compare its cell B with cell B from each row of SKTSheet, and if find them equal, to update E-H cells (SKTSheet.Ecell = SKTSheet.ECell + TempSheet.Ecell, same for F G and H), and increase SKTSheet.Icell by 1. Then I’d need that row deleted from TempSheet, so it would contain only rows that had no match in BCells with SKT after macro is done.
If it has any relevance, TempSheet would have about 20 rows, while SKT one has about 500 and growing.
I appreciate your help, thanks !
Last edited by CRL7 on Thu Sep 25, 2008 4:57 am; edited 1 time in total |
|
| Back to top |
|
 |
CRL7
General User
Joined: 12 Aug 2008
Posts: 24
|
| Posted: Thu Sep 18, 2008 6:58 am Post subject: |
|
|
Actually, I figured out how to do it.
Thanks all
PS: I don't know how to put this post as solved |
|
| Back to top |
|
|
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France
|
|
| Back to top |
|
|
CRL7
General User
Joined: 12 Aug 2008
Posts: 24
|
| Posted: Thu Sep 25, 2008 4:56 am Post subject: |
|
|
Aww, as I said my knowledge is very close to zero, I doubt my macros would be of any help to others, but here they are:
| Code: |
Sub IfNameIsSame
dim dispatcher as object
dim Doc, document As Object
Dim Sheet1, Sheet2 As Object
Dim Row As Object
Dim I, J, LstLn, scores As Integer
Dim NameCe1, NameCe2, Skor, NPl As Object
Dim CellE, CellF, CellG, CellH As Object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
Doc = StarDesktop.CurrentComponent
Sheet1 = Doc.Sheets(1)
Sheet2 = Doc.Sheets(2)
For I=0 to 1000
NameCe1 = Sheet1.getCellByPosition(0, I) ' get first name from new list
If NameCe1.String = "" then goto LabelEnd
Skor = Sheet1.getCellByPosition(3, I) ' get skor
For J=3 to 500
NameCe2 = Sheet2.getCellByPosition(0, J) ' get first name from list that is being updated
If NameCe2.String = "" then goto LabelNext ' if empty
If NameCe1.String=NameCe2.String then ' if match
CellE = Sheet2.getCellByPosition(4, J)
CellF = Sheet2.getCellByPosition(5, J)
CellG = Sheet2.getCellByPosition(6, J)
CellH = Sheet2.getCellByPosition(7, J)
If Skor.Value = 3 then CellE.Value = CellE.Value + 1
If Skor.Value = 2 then CellF.Value = CellF.Value + 1
If Skor.Value = 1 then CellG.Value = CellG.Value + 1
If Skor.Value = 0 then CellH.Value = CellH.Value + 1
NameCe1 = Sheet1.getCellByPosition(1, I)
NameCe1.String = "" ' delete used name from new list
End if
LstLn = J
Next J
LabelNext:
Next I
LabelEnd:
msgbox "Done"
' Print last row #
NPl = Sheet1.getCellByPosition(5, 2000)
NPl.Value = LstLn
End Sub
Sub AddNewNames
dim dispatcher as object
dim Doc, document As Object
Dim Sheet1, Sheet2 As Object
Dim Row As Object
Dim I, J, scores As Integer
Dim NameCe1, NameCe2, Skor As Object
Dim CellE, CellF, CellG, CellH As Object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
LabelSrd:
'-----------
Doc = StarDesktop.CurrentComponent
Sheet1 = Doc.Sheets(1)
Sheet2 = Doc.Sheets(2)
For I=3 to 1000
NameCe2 = Sheet2.getCellByPosition(1, I)
If NameCe2.String = "" then ' found first empty line
For J=0 to 1000
NameCe1 = Sheet1.getCellByPosition(1, J) ' get 1st new name from new list
If NameCe1.String = "" then goto LabelEnd2 ' if empty its done
NameCe2.String = NameCe1.String ' if not put it into updated list
Skor = Sheet1.getCellByPosition(3, J)
CellE = Sheet2.getCellByPosition(4, I)
CellF = Sheet2.getCellByPosition(5, I)
CellG = Sheet2.getCellByPosition(6, I)
CellH = Sheet2.getCellByPosition(7, I)
If Skor.Value = 3 then CellE.Value = CellE.Value + 1
If Skor.Value = 2 then CellF.Value = CellF.Value + 1
If Skor.Value = 1 then CellG.Value = CellG.Value + 1
If Skor.Value = 0 then CellH.Value = CellH.Value + 1
NameCe1.String = "" ' delete name from temp list
goto LabelSrd
Next J
Endif
Next I
LabelEnd2: msgbox "New Names added"
End Sub
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
