Ask Your Question

Macro needed, that picks up value of one cell to select a row [closed]

asked 2016-12-22 22:00:59 +0100

jjb gravatar image

My calc file contains two sheets: "daten" and "bearbeiten". In sheet "bearbeiten" cell A1 shows a number, say 150. Cell B1 contains text. I need a macro, that will pick up the number found in A1, e.g. 150, and use that number as row number in sheet "daten", i.e., copying the content of cell B1 to cell B150 in "daten". Can anyone help?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by jjb
close date 2016-12-23 13:56:36.112524

2 Answers

Sort by » oldest newest most voted

answered 2016-12-22 22:50:42 +0100

Craig22 gravatar image

updated 2016-12-22 22:52:20 +0100

Merry Christmas and a happy new year...

REM  *****  BASIC  *****
  Sub xCopy
  Dim oDoc as Object
  Dim oSheet1 as Object, oSheet2 as Object
  Dim oRange1 as Object, oRange2 as Object
  Dim sText as String   

    oDoc = ThisComponent
    oSheet1 = oDoc.Sheets.getByName("bearbeiten")
    oSheet2 = oDoc.Sheets.getByName("Daten")

    ' Source Range
    oRange1 =oSheet1.getCellRangeByName("A1")

    ' get Destination Range from Cell A1 in Sheet >>bearbeiten<<
    ' oRange1.Value-1 = row counter || minus 1, because the row count starts by 0
    oRange2 =oSheet2.getCellByPosition(1,oRange1.Value-1)

    ' write string from Sheet >>bearbeiten<< Cell B1 to Destination Range

    End Sub



edit flag offensive delete link more


Craig, the macro works beautifully. It is everything I wanted. Thank you so much.

jjb gravatar imagejjb ( 2016-12-23 13:55:16 +0100 )edit

answered 2016-12-23 00:11:01 +0100

Lupp gravatar image

Function calls can pass parameters. To control a Sub by variable values, I only know the way to use a fix range for the service. Feeding global variables of the module may also be a way. Otherwise everything has to be hard coded.
Thus I prefer functions over subs wherever possible (and if using custom code at all).

If you want a demonstration of the "Function-Do" fo the task under diskussion, please study the attached example. The functions contained therein were coded some time ago and were never used in production. The code is preliminary. Errors expected!

edit flag offensive delete link more


Thank you very much for your answer. It will be useful to me.

jjb gravatar imagejjb ( 2016-12-23 13:55:13 +0100 )edit

Question Tools

1 follower


Asked: 2016-12-22 22:00:59 +0100

Seen: 163 times

Last updated: Dec 23 '16