How to take a range of cells and put the entire range into another range

[Madmaxneo]

Basically I am trying to use the macros below that someone else gave to me with another set of cells but in a different way. But, it doesn’t work like it should and I am trying to figure out which line (or lines) is limiting how the macros work.
Let me explain how the macro below works for me and what I want it to do.

1. The mySourceCell has a dropdown list in it with a series of selections. I select one and if I like it I click a button that runs the macro below and it takes the text in mySourceCell and puts it into the first empty cell in the myTargetRange series of cells. It then clears out mySourceCell so I can make another selection if I so choose.
2. I now want to use that macro in a different button that will take the myTargetRange series of cells and move them all to another list (call it myOwnRange) that can and will have other entries in it. Then it will clear out the myTargetRange cells.
   3.The issue is it only moves the first cell in myTargetRange over to myOwnRange then clears out that first cell in myTargetRange, but it doesn’t do anything with the other cells at all.
   Could it be the oSourceCell line in the first macro below that is causing it to only move one line at a time?

REM  *****  BASIC  *****
Option Explicit

Sub PutDataIntoFirstEmptyRowOfTheTargetRange
 Dim oDoc as object
 Dim oSourceRange as object
 Dim oSourceCell as object
 Dim oTargetRange as object
 Dim oTargetCell as object
 Dim lColNr as long
 Dim sFormula as string  	
	oDoc = ThisComponent
	oSourceRange = GetNamedRange("mySourceCell", oDoc)
	oSourceCell = oSourceRange.ReferredCells.getCellByPosition(0,0)
	sFormula = oSourceCell.Formula			
	oTargetRange = GetNamedRange("myTargetRange", oDoc)
	lColNr = 0 'zero based numbering
	oTargetCell = GetFirstEmptyCellInAColumnOfRange(oTargetRange, lColNr)
	oTargetCell.Formula = sFormula
	oSourceCell.Formula = ""
end Sub
'_____________________________________________________________________________________________


Function GetNamedRange(sRange_name as string, optional oCalc_File as object) as object
 Dim oDoc, oRange as object
	If IsMissing(oCalc_File) then
		oDoc = ThisComponent
	else
		oDoc = oCalc_file
	end if 	
	If len(sRange_name) = 0 or sRange_name = "" then
		GetNamedRange = NOTHING
 		Exit function
 	End if
 	if oDoc.namedranges.hasByName(sRange_name) then 
		oRange = oDoc.NamedRanges.getByName(sRange_name)
		GetNamedRange = oRange
	else
		GetNamedRange = NOTHING
	end if		
end function
'_____________________________________________________________________________________________


Function GetFirstEmptyCellInAColumnOfRange(oTheRange as object, lColNr as long) as object
 Dim oCells as object
 Dim oCell as object
 Dim lRowNr as long 
 Dim i as long 	
 	oCells = oTheRange.ReferredCells
 	lRowNr = oCells.Rows.Count 	
 	For i = 0 to lRowNr-1
 		oCell = oCells.getCellByPosition(lColNr,i)
 		If  oCell.getType() = 0 Then 
 			GetFirstEmptyCellInAColumnOfRange = oCell
 			Exit function
 		end if 		
	next i	
end function
'_____________________________________________________________________________________________

I am hoping someone can explain this to me so I can try and figure out how to get it to work correctly (with some help of course).

[karolus]

@Madmaxneo

If len(sRange_name) = 0 or sRange_name = "" then

show me something which meets a but not b

[Madmaxneo]

Do you mean what meets this criteria = 0 in that code? If so what exactly do you mean?

[Wanderer]

@Madmaxneo

what exactly do you mean?

What exactly do you expect as result of len("")

[karolus]

sub main
	for each word in array( "abc" ,  "ab",  "a", "")
		if  word = "" then
			msgbox  "here is nothing to see" & chr(10) & "and its lenght is: " & len(word)
		else 
    		msgbox word & chr(10) & "and its lenght is: " &  len(word)
    	end if
	next
end sub

[mikekaganski]

@Madmaxneo

	If len(sRange_name) = 0 or sRange_name = "" then

The point that others are doing here is, that if and only if a string is "", its length is 0. Which means, that the three conditions below:

	If len(sRange_name) = 0 or sRange_name = "" then
	If len(sRange_name) = 0 then
	If sRange_name = "" then

are completely equivalent functionally; and that the first of them has two issues:

1. It is a bit slower, because it does two (equivalent) checks, where only one is needed;
2. It shows lack of understanding of the relation of string content to its length.

The latter seems plausible, given the nature of your question. A good suggestion could be to read some documentation (especially related to basics of BASIC )…

[Madmaxneo]

I am not sure what len("") is supposed to mean.
Right now I am not exactly sure how it relates to my query either. What I get from the examples using len is that if it equals 0 or " " then the result is nothing.