I'm trying to count the number of columns between two values in a row:
the maximum number in an array (e.g., max (A1:E1) and the last cell of
the array (e.g., E1).
So if the array is A1:E1, and B1 has the largest value, I need to
return 3 because there are 3 columns from B1 to E1 (including E1).
I have several dynamic named ranges that span multiple worksheets. The
ranges are named Sheet1Data, Sheet2Data ... up to Sheet4Data.
I need to create a macro that applies a bottom border to the last row
in each range. I've taken some code that loops through the *cells* in
each range (Dim c As Rang
I'd like to create a macro that adds a border to the bottom cells
(i.e., row) of two dynamic ranges.
e.g., Range 1 is based on this formula:
=OFFSET(WorkSheet1!$C$8,0,-1,MATCH(1E+306,WorkSheet1!$C$8:$C$265,1),
16)
A macro runs some calculations in this range, deletes some rows, and
so on, so the
Sent via BlackBerry by AT&T
-Original Message-
From: excel-macros+nore...@googlegroups.com
Sender: excel-macros@googlegroups.com
Date: Wed, 06 Oct 2010 23:25:47
To: Digest Recipients
Reply-To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Digest for excel-macros@googlegroups.co
Got it working! Here's the final code. I actually have two named
ranges on each worksheet, so the code performs the highlighting for
each.
###
Dim curWks As Worksheet
Dim r As Long
Dim Range1 As Range
Dim Range2 As Range
Set curWks = ActiveSheet
With curWks
If .Name = ("WS1") Then
Set Range
r = 2 To .Rows.Count Step 2
.Rows(r).Interior.ColorIndex = 15
Next r
End With
End With
On Sep 9, 12:11 pm, Kurt wrote:
> I don't think the conditional formatting option will work, because the
> size of the range changes depending on the data. In other words, there
> is no
I don't think the conditional formatting option will work, because the
size of the range changes depending on the data. In other words, there
is no fixed block to select to apply the conditional formula.
On Sep 9, 12:35 am, Dave Bonallack wrote:
> Hi,
>
> I assume your shading is currently done
I have four worksheets (WS1, WS2, etc.), and on each sheet a dynamic
name ranged (WS1Data, WS2Data, etc.). These named ranges always begin
on B8 but the columns and rows they span will vary based on the data
(so I can't use an absolute reference). The rows in the range are
alternatively shaded.
Ea
Posted it to the file list. It's called ISERROR Example.xls.
On Sep 1, 1:00 am, "C.G.Kumar" wrote:
> Kindly attach sample file.
>
> On Wed, Sep 1, 2010 at 12:12 AM, Kurt wrote:
> > Can't seem to get this working.
>
> > If A1 is a calculated err
Can't seem to get this working.
If A1 is a calculated error (e.g., #NULL) OR If A1 = "--- ", make
B1 = "NA"
Otherwise: If A1 < Y1, make B1 = "!", otherwise make B1 = "" (i.e.,
blank).
This is what I have in B1, but it always returns a #NULL error.
=IF(OR(ISERROR(A1),A1="---"),"NA",IF(A1
hanks much.
On Aug 26, 5:22 am, Dave Bonallack wrote:
> Hi Kurt,
>
> If your named ranges are really called WS1Data, WS2Data, WS3Data, WS4Data,
> then your code can be reduced to just a few lines.
>
> Sub NoData()
> Dim i As Integer, c As Range
> ' Cycle thr
I have 4 worksheets (WS1, WS2, etc.) with cells that get populated
with data from a linked source sheet. In Column B on each worksheet is
a dynamic range of cells (on WS1 it's "WS1Data," WS2 it's "WS2Data,"
etc.) When rows in the range are blank, I'd like to insert the word
"no data."
I can do thi
Sent via BlackBerry by AT&T
-Original Message-
From: excel-macros+nore...@googlegroups.com
Sender: excel-macros@googlegroups.com
Date: Sun, 22 Aug 2010 23:39:03
To: Digest Recipients
Reply-To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Digest for excel-macros@googlegroups.co
> Kurt wrote:
> > I am trying to duplicate the sort feature used in this tutorial:
>
> >http://www.contextures.com/xlSort02.html
>
> > The description of the first part of the code suggests that the code
> > creates the invisible rectangles, but I couldn't
I am trying to duplicate the sort feature used in this tutorial:
http://www.contextures.com/xlSort02.html
The description of the first part of the code suggests that the code
creates the invisible rectangles, but I couldn't get it to do this. (I
had the create the rectangles manually, ahead of ti
lication.Goto Reference:="MyRange"
> End Sub
>
> It is the Goto that I think you want
>
> Thanks,
> David
>
> On Jul 2, 1:28 pm, Kurt wrote:
>
> > I'm converting a lot of VBA code from using absolute references to
> > named ranges.
>
> &g
I'm converting a lot of VBA code from using absolute references to
named ranges.
Here is an example I'm not sure how to revise.
The original code references rows 2 to 259 in Column A. I have since
named A2:A259 "MyRange" but the original code refers to "iRow = 2 To
259". How can I change this to
You are a hero! Works perfectly. Let this code replace the 400 other,
more complicated examples of code I found that attempt to do the same
thing.
On Jul 1, 2:34 am, Stuart Redmann wrote:
> On 30 Jun. Kurt wrote:
>
> > > > 1. The comment in this code has as an example, &
> > 1. The comment in this code has as an example, "ShadeAlternateRows
> > Range("A1:D50"), 27, 2", but where in the actual code do you put this
> > information?
>
> That depends on when the highlighting should happen.
I'm most interested in learning how to do it manually (e.g., after
manually sel
I'm rather new to Excel macros so any help is appreciated.
I would like to highlight every other row in a range (A5:L40). I do
not want to use conditional formatting, because I'm already using some
in this range. So, I need to use a macro.
I've seen lots of macro samples to shade alternate rows,
20 matches
Mail list logo
