Macro Troubles

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Macro Troubles

QCIT3 MMisonora
Hi there,
I have an excel file with macros on it and have gotten it onto OpenOffice, but the coding for the macros on the file keep saying there is an error, so I was wondering if anyone can please inform me as to how I can fix these two lines in two separate codes.

       1. LastSn = rng.Find("*", rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False) .Row

       2. TargetCol = Target.Column


Thanks.


Sent from Outlook<http://aka.ms/weboutlook>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Macro Troubles

Andrew Pitonyak

Trying to remember which will get me flamed less, top posting or bottom
posting <sigh>.

Lets start with (2) because it is the easiest to answer.

So, the answer is, I have no idea, it depends on what object Target is.

Is this a Cell? A Range?

If this is a SheetRange, then you should be able to use
"Target.RangeAddress.StartColumn". If this is already a Range address,
then you should be able to use "Target.StartColumn".

I know this is not a cell address, or Column would already work. Does
it work if you use "Target.getCellAddress().Column"?

Sadly, you probably need to fix (1) before looking at (2).

Question (1) is more difficult because you want to perform a search.

I assume from the name that rng is a sheet range, Sadly, searching in
AOO is much more difficult than in Excel (using macros).

If you comment out the offending lines, does it complain if you use
something like:

rng.CreateSearchDescriptor()

If that does not cause a failure, then you have a sheet cell range and
you can create a search descriptor. I hvae no idea about the use of
Lookat or LookIn.

Here are some of the properties that are probably supported by your
search descriptor.

STRING  ImplementationName = ScCellSearchObj
STRING  ReplaceString =
BOOL  SearchBackwards = False
BOOL  SearchByRow = False
BOOL  SearchCaseSensitive = False
BOOL  SearchRegularExpression = False
BOOL  SearchSimilarity = False
INTEGER  SearchSimilarityAdd = 2
INTEGER  SearchSimilarityExchange = 2
BOOL  SearchSimilarityRelax = False
INTEGER  SearchSimilarityRemove = 2
STRING  SearchString =
BOOL  SearchStyles = False
INTEGER  SearchType = 0
BOOL  SearchWildcard = False
BOOL  SearchWords = False

I am guessing that this does a row based search, so you might begin
with something like:

Dim oSearchDescriptor
oSearchDescriptor = rng.CreateSearchDescriptor()
oSearchDescriptor.SearchByRow = true

Your macro searches in the direction last used (or so it seems), if
not, it seems that you can probably set SearchBackwards to true or false
depending on the direction that you want.

Beyond that I am only guessing. Is this looking for the text "*"? I
have no idea what your second parameter does there.

If you can fill out the search descriptor, you will probably need to
use something like this totally untested

   Dim oFound
   oFound = rng.findFirst(oDescriptor)
   Do While Not IsNull(oFound)
     ' Do something with the oFound object
     ' Off hand, it looks like you really only need to find the first
thing
     ' and then use it for something. Maybe you really just need to find
the last one, I can only guess.
     ' I am really guessing on this next line.
     oFound = rng.findNext(oFound, oDescriptor)
   Loop



On 23.06.2017 13:24, QCIT3 MMisonora wrote:

> Hi there,
> I have an excel file with macros on it and have gotten it onto
> OpenOffice, but the coding for the macros on the file keep saying
> there is an error, so I was wondering if anyone can please inform me
> as to how I can fix these two lines in two separate codes.
>
>        1. LastSn = rng.Find("*", rng.Cells(1), Lookat:=xlPart,
> LookIn:=xlFormulas, SearchOrder:=xlByRows,
> SearchDirection:=xlPrevious, MatchCase:=False) .Row
>
>        2. TargetCol = Target.Column
>
>
> Thanks.
>
>
> Sent from Outlook<http://aka.ms/weboutlook>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Macro Troubles

Hagar Delest-2
FWD to OP.

Le 23/06/2017 à 20:20, Andrew Pitonyak a écrit :

>
> Trying to remember which will get me flamed less, top posting or bottom posting <sigh>.
>
> Lets start with (2) because it is the easiest to answer.
>
> So, the answer is, I have no idea, it depends on what object Target is.
>
> Is this a Cell? A Range?
>
> If this is a SheetRange, then you should be able to use "Target.RangeAddress.StartColumn". If this is already a Range address, then you should be able to use "Target.StartColumn".
>
> I know this is not a cell address, or Column would already work. Does it work if you use "Target.getCellAddress().Column"?
>
> Sadly, you probably need to fix (1) before looking at (2).
>
> Question (1) is more difficult because you want to perform a search.
>
> I assume from the name that rng is a sheet range, Sadly, searching in AOO is much more difficult than in Excel (using macros).
>
> If you comment out the offending lines, does it complain if you use something like:
>
> rng.CreateSearchDescriptor()
>
> If that does not cause a failure, then you have a sheet cell range and you can create a search descriptor. I hvae no idea about the use of Lookat or LookIn.
>
> Here are some of the properties that are probably supported by your search descriptor.
>
> STRING  ImplementationName = ScCellSearchObj
> STRING  ReplaceString =
> BOOL  SearchBackwards = False
> BOOL  SearchByRow = False
> BOOL  SearchCaseSensitive = False
> BOOL  SearchRegularExpression = False
> BOOL  SearchSimilarity = False
> INTEGER  SearchSimilarityAdd = 2
> INTEGER  SearchSimilarityExchange = 2
> BOOL  SearchSimilarityRelax = False
> INTEGER  SearchSimilarityRemove = 2
> STRING  SearchString =
> BOOL  SearchStyles = False
> INTEGER  SearchType = 0
> BOOL  SearchWildcard = False
> BOOL  SearchWords = False
>
> I am guessing that this does a row based search, so you might begin with something like:
>
> Dim oSearchDescriptor
> oSearchDescriptor = rng.CreateSearchDescriptor()
> oSearchDescriptor.SearchByRow = true
>
> Your macro searches in the direction last used (or so it seems), if not, it seems that you can probably set SearchBackwards to true or false depending on the direction that you want.
>
> Beyond that I am only guessing. Is this looking for the text "*"? I have no idea what your second parameter does there.
>
> If you can fill out the search descriptor, you will probably need to use something like this totally untested
>
>   Dim oFound
>   oFound = rng.findFirst(oDescriptor)
>   Do While Not IsNull(oFound)
>     ' Do something with the oFound object
>     ' Off hand, it looks like you really only need to find the first thing
>     ' and then use it for something. Maybe you really just need to find the last one, I can only guess.
>     ' I am really guessing on this next line.
>     oFound = rng.findNext(oFound, oDescriptor)
>   Loop
>
>
>
> On 23.06.2017 13:24, QCIT3 MMisonora wrote:
>> Hi there,
>> I have an excel file with macros on it and have gotten it onto
>> OpenOffice, but the coding for the macros on the file keep saying
>> there is an error, so I was wondering if anyone can please inform me
>> as to how I can fix these two lines in two separate codes.
>>
>>        1. LastSn = rng.Find("*", rng.Cells(1), Lookat:=xlPart,
>> LookIn:=xlFormulas, SearchOrder:=xlByRows,
>> SearchDirection:=xlPrevious, MatchCase:=False) .Row
>>
>>        2. TargetCol = Target.Column
>>
>>
>> Thanks.
>>
>>
>> Sent from Outlook<http://aka.ms/weboutlook>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Loading...