You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of computer programmers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other programmers’ questions, win occasional prizes given to our best members, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
If I have a row range from A4 to HQ4 and I only require every third cell from A4 then D4 then G4 ...HO4 for searching.
How do I name it with out getting typists finger?
Each third cell has a change in value the other two between are equal to the third cell.
A4=101
B4=101
c4=101
d4=102
e4=102
f4=102
g4=103
etc.
Thanks
Bill
In your specific example you can actually populate the row with Excel Formulae and no VBA. You need to do the following things (ignoring the double quotes - they are there to delineate what to type):
1) Type the value "101" in cell A4
2) Type the formula "=A4" into cell B4 and fill right into cell C4
3) Type the formula "=A4+1" into cell D4 and fill right into every cell over to HQ4
If what you're after is a slightly less predictable new value in every thrid cell then you might want to consider setting up a lookup table. The formula "=INT((COLUMN(A1)+2)/3)" written in a row of cells will produce a periodic incremental lookup value index which could then be used with the INDEX function on a custom table to pull through less predictable periodically changing values - i.e. Type a table of values into cells A5 to A22 and then write the following formula in Cell A4 to be filled right "=INDEX($A$5:$A$22,INT((COLUMN(A1)+2)/3))".
You can also do stuff with VBA but it sounds like this is not the most appropriate solution
Name a range simply for each third cell
Linear Mode
