macro to check if cells have values in them...then

Hi - I am looking for a macro that will check 2 things....then do something. 
The first is if a range of cell values in are blank.  lets say 
Sheets("Sheet1").Range("D6:G48").
Next is if a particular cell  contains a given value , 
Sheets("sheet1").Range("E5") should equal "Year 1" if true. 
If both these are true then I want the macro to copy a range of cells from 
Sheets("Sheet2").Range("H6:H48") to Sheets("Sheet1").Range("D6:D48").

I tried to use IsEmpty but it seems to return a "False" if a range of cells 
are being tested.

Thanks for any help - Jim A
0
JimA (42)
1/8/2009 11:21:49 PM
excel 39879 articles. 2 followers. Follow

4 Replies
644 Views

Similar Articles

[PageSpeed] 35

Hi Jim

One way
Sub test()
    If WorksheetFunction.CountA(Sheets("Sheet1").Range("D6:G48")) = 0 _
       And UCase(Sheets("Sheet1").Range("E5")) = UCase("Year 1") Then
        MsgBox "true"
    Else
        MsgBox "false"
    End If
End Sub

-- 
Regards
Roger Govier

"Jim A" <JimA@discussions.microsoft.com> wrote in message 
news:97F594E7-BC27-437D-9ABA-B8C3F6751053@microsoft.com...
> Hi - I am looking for a macro that will check 2 things....then do 
> something.
> The first is if a range of cell values in are blank.  lets say
> Sheets("Sheet1").Range("D6:G48").
> Next is if a particular cell  contains a given value ,
> Sheets("sheet1").Range("E5") should equal "Year 1" if true.
> If both these are true then I want the macro to copy a range of cells from
> Sheets("Sheet2").Range("H6:H48") to Sheets("Sheet1").Range("D6:D48").
>
> I tried to use IsEmpty but it seems to return a "False" if a range of 
> cells
> are being tested.
>
> Thanks for any help - Jim A 

0
Roger
1/8/2009 11:45:54 PM
That worked great!
how can I get this macro to check for "Year 1" and if False move on to "Year 
2", (then "Year 3" ect,) and its corresponding range of cells?
When it finds the Year value to be true it can then check for blank cells in 
its corresponding range.  ... then copy.

I really appreciated your help nd quick response - Jim A



"Roger Govier" wrote:

> Hi Jim
> 
> One way
> Sub test()
>     If WorksheetFunction.CountA(Sheets("Sheet1").Range("D6:G48")) = 0 _
>        And UCase(Sheets("Sheet1").Range("E5")) = UCase("Year 1") Then
>         MsgBox "true"
>     Else
>         MsgBox "false"
>     End If
> End Sub
> 
> -- 
> Regards
> Roger Govier
> 
> "Jim A" <JimA@discussions.microsoft.com> wrote in message 
> news:97F594E7-BC27-437D-9ABA-B8C3F6751053@microsoft.com...
> > Hi - I am looking for a macro that will check 2 things....then do 
> > something.
> > The first is if a range of cell values in are blank.  lets say
> > Sheets("Sheet1").Range("D6:G48").
> > Next is if a particular cell  contains a given value ,
> > Sheets("sheet1").Range("E5") should equal "Year 1" if true.
> > If both these are true then I want the macro to copy a range of cells from
> > Sheets("Sheet2").Range("H6:H48") to Sheets("Sheet1").Range("D6:D48").
> >
> > I tried to use IsEmpty but it seems to return a "False" if a range of 
> > cells
> > are being tested.
> >
> > Thanks for any help - Jim A 
> 
> 
0
JimA (42)
1/9/2009 4:54:00 AM
Hi Jim

What is "it's corresponding range"
Are you saying that Sheets("Sheet1").Range("D6:G48") only belongs to Year 1, 
and a different range of cells needs to be checked for Year 2, Year 3 etc.?
If so, what determines the range? Is it an offset from the first range?
Does the range you want to copy change with each Year?

The actual copying part is a one liner

Sheets("Sheet2").Range("H6:H48").Copy Sheets("Sheet1").Range("D6")

Sub test()
    If WorksheetFunction.CountA(Sheets("Sheet1").Range("D6:G48")) = 0 _
       And UCase(Sheets("Sheet1").Range("E5")) = UCase("Year 1") Then
        Sheets("Sheet2").Range("H6:H48").Copy 
Sheets("Sheet1").Range("D6:D48")
   End If
End Sub




-- 
Regards
Roger Govier

"Jim A" <JimA@discussions.microsoft.com> wrote in message 
news:0D36F1D6-4202-4A11-9F37-9114EF01A5EB@microsoft.com...
> That worked great!
> how can I get this macro to check for "Year 1" and if False move on to 
> "Year
> 2", (then "Year 3" ect,) and its corresponding range of cells?
> When it finds the Year value to be true it can then check for blank cells 
> in
> its corresponding range.  ... then copy.
>
> I really appreciated your help nd quick response - Jim A
>
>
>
> "Roger Govier" wrote:
>
>> Hi Jim
>>
>> One way
>> Sub test()
>>     If WorksheetFunction.CountA(Sheets("Sheet1").Range("D6:G48")) = 0 _
>>        And UCase(Sheets("Sheet1").Range("E5")) = UCase("Year 1") Then
>>         MsgBox "true"
>>     Else
>>         MsgBox "false"
>>     End If
>> End Sub
>>
>> -- 
>> Regards
>> Roger Govier
>>
>> "Jim A" <JimA@discussions.microsoft.com> wrote in message
>> news:97F594E7-BC27-437D-9ABA-B8C3F6751053@microsoft.com...
>> > Hi - I am looking for a macro that will check 2 things....then do
>> > something.
>> > The first is if a range of cell values in are blank.  lets say
>> > Sheets("Sheet1").Range("D6:G48").
>> > Next is if a particular cell  contains a given value ,
>> > Sheets("sheet1").Range("E5") should equal "Year 1" if true.
>> > If both these are true then I want the macro to copy a range of cells 
>> > from
>> > Sheets("Sheet2").Range("H6:H48") to Sheets("Sheet1").Range("D6:D48").
>> >
>> > I tried to use IsEmpty but it seems to return a "False" if a range of
>> > cells
>> > are being tested.
>> >
>> > Thanks for any help - Jim A
>>
>> 
0
Roger
1/9/2009 8:06:11 AM
Thanks for replying -
Yes, Sheets("Sheet1").Range("D6:G48") only belongs to Year 1 and 
range("D6:G48") need to be checked for being empty.
Sheets("Sheet1").Range(""I6:L48") belongs to Year 2 and range("I6:L48") need 
to be checked for being empty.
and so on...through Year 5.

If false I am trying to get the macro to move on to the next year and its 
range of cells.

The range I want to copy TO changes with each year.  Example:
Year 1 would copy to Sheets("Sheet1").Range("D6:D48")
Year 2 would copy to Sheets("Sheet1").Range("I6:I48") and so on...

I am having trouble using looping, so I have been trying to write this in a 
series of IF statements.  This seems like it can be problematic.

Thanks - Jim Ayers





"Roger Govier" wrote:

> Hi Jim
> 
> What is "it's corresponding range"
> Are you saying that Sheets("Sheet1").Range("D6:G48") only belongs to Year 1, 
> and a different range of cells needs to be checked for Year 2, Year 3 etc.?
> If so, what determines the range? Is it an offset from the first range?
> Does the range you want to copy change with each Year?
> 
> The actual copying part is a one liner
> 
> Sheets("Sheet2").Range("H6:H48").Copy Sheets("Sheet1").Range("D6")
> 
> Sub test()
>     If WorksheetFunction.CountA(Sheets("Sheet1").Range("D6:G48")) = 0 _
>        And UCase(Sheets("Sheet1").Range("E5")) = UCase("Year 1") Then
>         Sheets("Sheet2").Range("H6:H48").Copy 
> Sheets("Sheet1").Range("D6:D48")
>    End If
> End Sub
> 
> 
> 
> 
> -- 
> Regards
> Roger Govier
> 
> "Jim A" <JimA@discussions.microsoft.com> wrote in message 
> news:0D36F1D6-4202-4A11-9F37-9114EF01A5EB@microsoft.com...
> > That worked great!
> > how can I get this macro to check for "Year 1" and if False move on to 
> > "Year
> > 2", (then "Year 3" ect,) and its corresponding range of cells?
> > When it finds the Year value to be true it can then check for blank cells 
> > in
> > its corresponding range.  ... then copy.
> >
> > I really appreciated your help nd quick response - Jim A
> >
> >
> >
> > "Roger Govier" wrote:
> >
> >> Hi Jim
> >>
> >> One way
> >> Sub test()
> >>     If WorksheetFunction.CountA(Sheets("Sheet1").Range("D6:G48")) = 0 _
> >>        And UCase(Sheets("Sheet1").Range("E5")) = UCase("Year 1") Then
> >>         MsgBox "true"
> >>     Else
> >>         MsgBox "false"
> >>     End If
> >> End Sub
> >>
> >> -- 
> >> Regards
> >> Roger Govier
> >>
> >> "Jim A" <JimA@discussions.microsoft.com> wrote in message
> >> news:97F594E7-BC27-437D-9ABA-B8C3F6751053@microsoft.com...
> >> > Hi - I am looking for a macro that will check 2 things....then do
> >> > something.
> >> > The first is if a range of cell values in are blank.  lets say
> >> > Sheets("Sheet1").Range("D6:G48").
> >> > Next is if a particular cell  contains a given value ,
> >> > Sheets("sheet1").Range("E5") should equal "Year 1" if true.
> >> > If both these are true then I want the macro to copy a range of cells 
> >> > from
> >> > Sheets("Sheet2").Range("H6:H48") to Sheets("Sheet1").Range("D6:D48").
> >> >
> >> > I tried to use IsEmpty but it seems to return a "False" if a range of
> >> > cells
> >> > are being tested.
> >> >
> >> > Thanks for any help - Jim A
> >>
> >> 
> 
0
JimA (42)
1/9/2009 5:36:00 PM
Reply:

Similar Artilces:

When I am in one cell highlight another
Hello, When I am in one cell highlight another. Lets say that if my cursor is in A1 I want D1 highlighted or with another cursor on it. Then if I move to A2, D2 should be the one with another cursor or highlighted. Thank you for your help, Jose Juan Diaz hi, Jose Juan ! > When I am in one cell highlight another > ... if my cursor is in A1... D1 highlighted or with another cursor on it. > ... if I move to A2, D2 should be the one with another cursor or highlighted. 'put' a cursor on non-active-cell... [I don't think it's possible] :( to highlight 'D' wh...

Cells print so small I cannot read numbers. How do I fix?
I have been working with page break. Now I have the grid on 1 page..but it is far to small to read. now when I try to spread it back to 2 pages, it just takes the same tiny microscopic type and spreads it into 2 pages. I am stuck printing tiny type. How can I get the grid cells back to a size that is readable. It sound like you have selected Fit to 1 page in File > Page setup > Page > Scaling. Either select to fit it to 2 pages or select Adjust to 100% size -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Repl...

referencing to worksheet names in macro for each new worksheet inserted
Hi I created a code to insert new worksheets and rename them according t values on the new worksheet itself. Say in Cell D1, i have th worksheet name. My question is when i want to refer to this worksheet in subsequen coding, how should i code it? For eg, How should i write the ???? for Sheets("????").select? Would creatin the a variable to store the names help? Thanks in advance Ken -- Message posted from http://www.ExcelForum.com After inserting your new worksheet set it's name equal to a variable. For example SHEETS.ADD VWORKSHEET = ACTIVESHEET.NAME This method ...

How to check the style of window is not WS_EX_APPWINDOW ?
Hello There How can I check whether the extended style of window is not WM_EX_APPWINDOW.( Means it won't have entry in taskbar when it visible. I can check whether the it has WS_EX_APPWINDOW style like this if (::GetWindowLong(hwnd, GWL_EXSTYLE) & WS_EX_APPWINDOW) TRACE("App Window\n"); But how can I do the reverse thing ? Example: 0010101 Real 0110101 Mask The Bitwise AND gives a 1 if the real and the mask are 1, such as 00101100 00100100 AND ---------- 00100100 In this situation, we know that at least some of the mask is true, -- now -- Take the binary number...

Process all cells in a (user) selection
I am using this code to give me cell by cell access to a code defined selection; Dim aCell As Range For Each aCell In Sheet4.Range("A4:A34") ' Do Stuff Here Next aCell which processes every cell in the A4:A34 Range How do I do the same thing for a selection drawn by the user before pressing my 'Process' button ? Thanks On 05 May 2010 11:10:21 GMT, Isis <isissoft@NOSPAMbtinternet.com> wrote: >I am using this code to give me cell by cell access to a code defined >selection; > >Dim aCell As Range >For Each aCell In S...

VB code for Macro
I have set up a rule on my InBox to check for specific words and move emails to my Work folder. Now I review emials in my Work folder and drag and drop them into 1 of 4 folders based on a number 1-4. After clicking on the folder, I need to perform the following on each of the four folders: Click on first email in the folder Clt+A (to select all the emails in the folder) Ctl+C (to copy) Drag the selections to a folder name HH Click #_Button (customized button set to send an email) Ctl+v (to paste the contents in the body of the email) Click Send Steps without the comments: Click folde...

How to Change Value of Active Control
I'm writing a routine to change the value of any Active Control to null. I can get the name of the control using ActiveControl.Name but I can't figure out the syntax to change the value of this control. I've tried assigning it to variables but variables aren't working in a: Forms![variable]![variable] = "" Help "Sondreli" <Sondreli@discussions.microsoft.com> wrote in message news:AA05A840-2AFE-4F98-ABE9-5D2DD17AE0B8@microsoft.com... > I'm writing a routine to change the value of any Active Control to null. > I > can g...

Max of value from DataGroup2 within DataGroup1
Hello - I have a table with data as follows below. I am trying to build a query that will give me the record with MAX of specDiffMax [value] for each TestFreq [DataGroup 2] within each TestNum [DataGroup1]. ID TestNum TestFreq specDiffMax -------------------------------------------- 4889683 Test 1 1710 3.669998 5123289 Test 1 1710 2.882999 4817314 Test 1 1710 3.102001 5134007 Test 1 1710.2 3.573002 4896056 Test 1 1710.2 3.355 4914480 Test 1 1710.2 3.515999 4889685 Test 2 1710.4 3.333 4896057 Test 2 1710.4 3.450001 4914481 Test 2 ...

think cell program
is anyone familiar with a program called think cell? Any thoughts? (powerpoint v 2003). Is this an add-in? thanks Sara It's very good. You can "try for free" from their website: http://www.think-cell.com/ Recent interview with one of the founders on Indezine: http://blog.indezine.com/2009/12/think-cell-conversation-with-markus.html -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/3...

Help with ShowFilter Macro
I'm trying to use this ShowFilter UDF written by Tom Ogilvy (see bottom of post). It says to use... =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) ....in a cell to show the criteria for Column B. For one thing, I don't understand the cells B2 and B3 business. What is supposed to be in those cells? I would like this function to appear in the cell directly above or below the Autofilter menu cell. How do I change the function if the Autofilter criteria menu is in, cell A3, for example, and I want the criteria (this function) to appear just above, in cell A2? After trying all so...

Validation Rule for field values
I have a database that includes a phone number field. I want to prevent a user from creating a new record that contains a phone number that is currently in a record that exists in the database.I am using a form for data entry into the table. Somehow I'd like for there to be a check to see if the phone number typed into the phone number field to be checked against existing phone numbers in the database.I've researched various sources but haven't been able to find the solution. I'm not familiar with VB so any replies that suggest using that will probably not work for me. Sorry, j...

How do I convert time (hh:mm) to value ($$) in Excel?
Would like to calculate cost of time. Eg. Cost for production down time per minute is $100. Says production doen for 3.5 hrs, what is formula shall I apply in order to generate the cost (in $$). =3.5*60*100 "ahfen79" wrote: > Would like to calculate cost of time. Eg. Cost for production down time per > minute is $100. Says production doen for 3.5 hrs, what is formula shall I > apply in order to generate the cost (in $$). =(3.5/24)*60*100 -- Regards Dave Hawley www.ozgrid.com "ahfen79" <ahfen79@discussions.microsoft.com> wrote in ...

Newbie Cell Reference Question...
Hello, I apologize if this question has been answered before, but I'm no quite sure what to search for as I'm not very good with excel. I'm making a spreadsheet where one sheet references another. I'll tr to explain this as best as possible.... I'm creating a spreadsheet for a fantasy basketball league. I have "Data" sheet that contains data for all players. I have another sheet "Teams" that has all the players on each team. Column B contains th players name, and column C contains a number that corresponds to th row this player is on in the dat...

Macros #35
Hi I run a daily download from another system, via a .txt file into Excel. Each day the WorkSheet has a different name ie A3_12_11_04 then tommorow it will be A3_13_11_04 etc to represent the date it was downloaded. I then have to create a macro and pull off some of of the data on a daily basis...this is where my problem arrisses. How can my Macro recognise the different Worksheet name on a daily basis? My 2 thoughts would be to get data from another open worksheet or be able to put a promt in my Maco to search the name of the Worksheet. Confused...I am!! Any Help would be welcome. ...

macro
hi, i have various worksheets and within that worksheet there are cells having #DIV/0!. I want this to be replace by zero. i know there is a formula which will give out zero but if someone can write a macro would good. Thanks Sub ErrorTrapAdd() Dim mystr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=IF(ISERROR*" Then mystr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=IF(ISERROR(" & mystr & "),0," & mystr & ")" ...

Getting an UnBound control value into a Table field??????
I have a form that calculates a production rate in "parts per hour". This form needs to be able to differentiate between Line work, Cutter work, Side work, and Blister work. I created a drop down list for selecting the "Study Type" as listed above. I then created a text box for each "study type". When I select the "study type" each text box checks to see if the selection applies to it and makes the calculation if it does apply. The code for the "Line" study is as follows: =IIf([StudyType]="Line",2700?/[SecondsPerPart])...

Cell background shading in Excel
Why is it that the background shading colors available under the Paintbucket Icon chjange from file to file? Also, how can I add some of the colors to the paintbucket that are availabe under the format/cells/pattern pulldown? Colors are a Workbook-level property, so you can change the color palette for any workbook. Choose Tools/Options/Colors... and modify the colors to your hearts' content. You can also import another workbook's color palette from that dialog. Note however, that there are only 56 bins in the color palette, so you can only use 56 colors at a time. The paint b...

Sum every other cell?
I own a used-book shop. I have an Excel SS to track how many books pe day I sell in each of 28 categories and the $$$ I take in for eac category. So there's two columns for each day: #books & $$$. therefore want to add every odd-numbered cell in a row to get the tota number of books for a category in a given period of days and ever even-numbered cell in the same row for the the total $$$. Is there simple formula or function for this -- bookmanj ----------------------------------------------------------------------- bookmanjb's Profile: http://www.excelforum.com/member.php?acti...

Excluding multiple checking accounts from budget totals?
Howdy! Running Money06, and I have two checking accounts synching through Bank of America. Everything there is working well, but one thing that I dont like is that the totals for BOTH accounts are added together. I have two accounts, 'personal' and 'class', and both accounts are shown in the net balance statements, the 'spending by catagory' chart on the home page etc. I would like to keep synched with my class account, but want it excluded from all of the balances.. any suggestions? "Raichean" <Raichean@discussions.microsoft.com> wrote in mes...

Return a "" value from a formular
Any help greatly appreciated: I have 2 formulars that works, but I want them to return nothing eg "" when there is nothing entered into a cell, at the moment they return the 'false' value. My formulars are: =IF(O12>O11,"End milestone deadline passed","OK") =IF(AND(O12>O11,OR(D12>D11,E12>E11,F12>F11,G12>G11,H12>H11,I12>I11,J12>J11,K12>K11,L12>L11,M12>M11,N12>N11)),"End milestone passed and 'Actual's' late","End milestone passed but 'Actual's' OK") =IF(isblank(O12),&...

Sum of Top Values in Access Report
In an Access report, I'm presenting the top 15 cost values in the detail section. I have a counter to do start a new print page. That is working, but I also want to present the group total (=sum[netofreturns]) with the total of the top 15 values [top15only] and calculate the percentage of the top 15 to the group total (top 15/group total) in the group footer. ...

Help Required with Macro to Manipulate Data
I have a spreadsheet(s) with the following data on it (this is imported from a text file) A B C D 1 Branch Date Total 2 4501 030204 29 3 4 4501 030204 14 5 6 4502 030204 331 7 8 4502 030204 52 9 10 4503 030204 54 11 12 4503 030204 85 Hi Alay and what do you want to achieve :-) -- Regards Frank Kabel Frankfurt, Germany "Alan T >" <<Alan.T.12jthe@excelforum-nospam.com> schrieb im Newsbeitrag news:Alan.T.12jthe@excelforum-nospam.com... > I have a spreadsheet(s) with the following data on it (this is imported ...

ERROR check if a date set entered violates a perviously entered date set
I am devloping an Excel program that has a list of date sets, not necessarily in sequencial order. What I want is an ERROR CHECK if a date set violates any date set previously entered. What I was looking for was that a date set could not be typed in as follows: 1/10/04 to 4/20/04 2/10/04 to 3/20/04 The second date set falls within the first set and should be invalid setting off a message or an alert of an invalid entry. I am interested in making it idiot proof so that one could not enter date sets within or covering dates sets already entered. This one really throws me, Please help. ...

MERGE CELLS
I have Name, PO Box, street address, city, state, zip across a row in 6 seperate cells/columns. I want to have this format in 1 cell: Name PO Box Street address City, State Zip STEVE wrote: > I have Name, PO Box, street address, city, state, zip across a row in 6 > seperate cells/columns. > > I want to have this format in 1 cell: > > Name > PO Box > Street address > City, State Zip > You do realize that this will screw up your ability to sort the data. A better solution would be to describe what you want to do with the data. You may not need it in the ...

How do I format a cell to auto date
I would like to have a date automatically enter itself each time I open the spreadsheet. Is there a way to do this? Any help would be appreciated. Thanks Hi George Use a function like this one for todays date =TODAY() -- Regards Ron de Bruin http://www.rondebruin.nl "George" <George@discussions.microsoft.com> wrote in message news:7EF21130-EBD9-41C2-8CB5-5723BE40CBB1@microsoft.com... >I would like to have a date automatically enter itself each time I open the > spreadsheet. Is there a way to do this? Any help would be appreciated. > Thanks ...