need help transferring sheet code to module

[kevinu]

I have this code i've been using in a sheet but want to put it in a module but I keep getting errors. Its having issues with the (ByVal ) statements after the sub so my ws and dest are not defined correctly. Not sure what it needs.

Please Login or Register  to view this content.

[JLGWhiz]

You might want to change the 'ByVal' to 'ByRef' if you are calling the procedure and supplying those values on the call. eg.

Please Login or Register  to view this content.

ByVal is used when VBA can automatically detect a value, such As the Selected cell, or ActiveSheet. ByRef is used when the user supplies the values. If you have the code in a public module, then VBA cannot detect the sheet nor the range on its own.

[kevinu]

ok that makes sense. Thanks. The code that calls on this is looking at several arrays so the range changes for each range processed, 6 in total. I tried to have ws and dest change with each array but couldnt figure it out. Here is the parent code.

Please Login or Register  to view this content.

I'm thinking i can set ws and dest to the value of e but not sure how to get the range of e. CStr(e(0)) gives me the sheet name but not the range.

Sorry i didnt post this code before, probably helps to know whats going on better.

[JLGWhiz]

Where you call

Please Login or Register  to view this content.

it would equate to

Please Login or Register  to view this content.

I think you might be better off using two arrays, one for the names and one for the ranges. Say arrays e & f

Then you could do something like

Please Login or Register  to view this content.

[kevinu]

would that work in a module? and would i just set the ws or worksheet to e0 and the range to f0 so i can bypass the byval entry?

[JLGWhiz]

would that work in a module? and would i just set the ws or worksheet to e0 and the range to f0 so i can bypass the byval entry?

It would work like you are trying to make it work now, but using ByRef in the called macro. The arrays would be like

Please Login or Register  to view this content.

Then the call would be

Please Login or Register  to view this content.

and would then make your values int the called macro equate to ws = Name1 and dest = Range("A1").

[kevinu]

JLG, Im having trouble getting it to work. Can you show me how it should look in this code?

Please Login or Register  to view this content.

if this is the code in a sheet how should it look in a module?

[JLGWhiz]

I don't believe my suggestion is going to work either. I don't feel like tackling it tonight, but I will try to straighten it out tomorrow. I am sure we can come up with a solution that will work better than the way it is. One of the main problems is trying to use two different data types on the same variable. That is a definite no-no.

[kevinu]

no problem, im in no rush. Thanks for looking at it.

[JLGWhiz]

Here is what is happening:

Please Login or Register  to view this content.

1. e becomes a sub-array of the main array in the For loop as a variant data type
2. e(0) produces the String value of the sub-array first item.
3. e(1) produces the cell value of the sub-array second item.
4. test e(0), e(1) then equates to

Please Login or Register  to view this content.

5. The values will not be invoked into the test macro using ByVal syntax because because the array values are not selected.
6. In the test macro both the 'ws' and 'dest' variables would need to be declared as Variant to avoid generating an error when called from the ComboBox_Change macro.
7. The only place I see the 'dest' variable used in the 'test' macro is dest = ws, This appears to be putting the ws value in the dest cell. If That is all that it does, then that could be handled in the change macro and eliminated from the test macro altogether.
8. If you eliminate the dest variable, then you can modify your For loop, using a single array for only the Strings and not the corresponding ranges.
9. But you would need to add in a code segmenrt in the change macro to put compensate for the dest = ws statemtent that had been eliminated from the test macro.
10. Your loop would then look like

Please Login or Register  to view this content.

Then your title line for the test macro

Please Login or Register  to view this content.

For repalcing the dest = ws you could use a slect case in the change macro like

Please Login or Register  to view this content.

There might be better ways of doing this, but I am trying to minimize the rewrite of your code.

[kevinu]

ok i see. I just couldn't for the life of me figure out how to assign 'dest' the range. Looks like a case select can do that nicely. Thanks for your time and effort

[JLGWhiz]

You're welcome,
regards, JLG