Creating range using addressed cells??

[Ealglez]

Hi everyone,

I am trying to set up a logic to identify the trends that should be copied into a different workbook.

So far, I can identify where the trend is, but I am not able to set up the range, probably due to a variable definition.
My attempt is to find the name I want (cll1) in w/s "ACCLIQ_T_Input". Select the cell which is 2 rows bellow and 1 column to the left (cll3)
Then the range to be selected for copying in another workbook will be from cll3, to cll3+1 (2 columns) and to the last row of the trend (dynamic).

This is my attempt so far... but I cannot set the range from what I have at the moment.

Any help will be much appreciated...

        Dim cll1 As Range
        Dim cll3 As Integer
        Dim fnd1 As String
                       
         
         Set TC = Sheets("ACCLIQ_T_Input").Rows(1)
         Set cll1 = Sheets(Sheets.Count).Cells(2, 2)
        
         fnd1 = TC.Find(cll1)
         cll3 = Sheets("ACCLIQ_T_Input").Range(Sheets("ACCLIQ_T_Input").Cells(1, 1), Sheets("ACCLIQ_T_Input").Cells(2, Columns.Count).End(xlToRight)).Find(fnd1).Offset(2, -1)

This is part of a loop, hence the name Sheets(Sheets.Count)

Thanks very much!

 

[williamgowtama]

change cll3 to this

cll3 = Range(Sheets("ACCLIQ_T_Input").Cells(1, 1), Sheets("ACCLIQ_T_Input").Cells(2, Columns.Count).End(xlToRight)).Find(fnd1).Offset(2, -1)

 

[Ealglez]

Hey, thank you very for your time.
However, it is not working

The cll3 if just to identify WHERE is the range I want.

Let's put a more clear example:
- The trend are time vs. variable XX. Both columns have to be copied in the other worksheet.
- On top of time for each trend I have "case" and on top of variable XX I have the name of the case: "Case 1", "Case 2", etc. -this is in row 1 and trends start in row 3 from time=0hours
- Imagine I need the trend for "case 2" which is in columns E (time) and F (variable XX). Cases are not in order, hence my problem.
- With may code, cll3 will give me a value of zero, i.e. value in cell E3.
- But the range I need is the whole trend, i.e. E3:F - to the end of the trend, which varies.
- How can I select the corresponding range then???

I have tried to address cll3, which gives me cell $E$3, but I still cannot manage to define the trend range...

I hope this is clearer. Please let me know if not. Thank you very much for your help!!!

E

 

[williamgowtama]

i think i will need to see your sheet, and the codes in order to help you. but,
for the range of trend, you have to loop it from E3 till F by using "for".eg:
for i=3 to lastrow

next i
by addressing the range, try to use this instead:

Dim a As String
a = Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False)

it will results in "E3" insted of "$E$3" which makes it dynamic address.
Remember, the address is in "String" not "range". If you want to use it,

Sheets("Sheet1").Range(a).Select

 

[Ealglez]

Hey,

Thanks. Sure - how can I send the sheets though?

E

 

[williamgowtama]

You can send it to my email, or upload it to a filehost
william_great10@yahoo.co.id

 

[Ealglez]

Hey,

Thanks very much! I have realised though that what I wanted to do does not make much sense since when you extract the trends from the program the name is longer than the case name you will have... So I think rather than let the code to check for this the user can check it faster by checking that the data is in the same order as the case list.

Thank you very much for your time! Really really appreciated!
E