Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,814
Default Using a count in a range name

I have a macro that changes values in wks2 in range b1:b100. Every time the
data changes I want to copy the values in wks1, always beginning on row 3 but
the column number changes with a count. I can't figure out how to modify the
following code:

col=5

Worksheet("wks2").Range("b1:b100").Select
Selection.Copy
Sheets("wks1").Select

****on the next line I want to use "col"****
Range("D3").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 427
Default Using a count in a range name

One way to do it: create a named range called Col and store the value
5 to it, then add this code to your routine

Range("a3").Select 'use this instead of Range("D3").Select
ActiveCell.Offset(0, Mid(Names("Col").Value, 2,
Len(Names("Col").Value))).Select
Names("Col").Value = Evaluate(Names("Col").Value) +1

This starts at A3, offsets by the value of Col, then augments Col by
one for the next time it runs. The "ActiveCell.Offset" line contains
the MID function to strip the = sign out of Names("Col").Value.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum or Count using set range Rayasiom Excel Worksheet Functions 3 April 26th 07 02:26 PM
How do I use the count if function for a range from 40-60 ? April Excel Worksheet Functions 2 January 20th 06 04:18 AM
How to count dates within a certain range in a column with mutiple date range entries Krisjhn Excel Worksheet Functions 2 September 1st 05 01:59 PM
count no. of entries in range for a day DeeJay30 Excel Worksheet Functions 1 June 22nd 05 08:47 AM
Count cells in one range based on parameters in another range dave roth Excel Worksheet Functions 2 March 29th 05 05:33 PM


All times are GMT +1. The time now is 03:23 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"