Creating range using addressed cells??

Ealglez

New Member
Joined
Mar 13, 2015
Messages
28
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...

Code:
        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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
change cll3 to this
Code:
cll3 = Range(Sheets("ACCLIQ_T_Input").Cells(1, 1), Sheets("ACCLIQ_T_Input").Cells(2, Columns.Count).End(xlToRight)).Find(fnd1).Offset(2, -1)
 
Upvote 0
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
 
Upvote 0
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:
Code:
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,
Code:
Sheets("Sheet1").Range(a).Select
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top