OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] Rows within 2 sheets, finding match and updating

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
CRL7
General User
General User


Joined: 12 Aug 2008
Posts: 24

PostPosted: Thu Sep 18, 2008 3:48 am    Post subject: [Solved] Rows within 2 sheets, finding match and updating Reply with quote

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
View user's profile Send private message
CRL7
General User
General User


Joined: 12 Aug 2008
Posts: 24

PostPosted: Thu Sep 18, 2008 6:58 am    Post subject: Reply with quote

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
View user's profile Send private message
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Fri Sep 19, 2008 7:16 am    Post subject: Reply with quote

when you read your own thread you have an "edit" button allowing to change the title.
You can also give information on how do you solve your problem.
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide
Back to top
View user's profile Send private message Visit poster's website
CRL7
General User
General User


Joined: 12 Aug 2008
Posts: 24

PostPosted: Thu Sep 25, 2008 4:56 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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