help simple cell question that has me stumped

Mausten

New Member
Joined
May 14, 2007
Messages
15
Hi there,

Can any help with this?

What I need is to check each value of row ai, column 70 and if the value is greater then forty i paste a value in row ai, column 78. If not no action.

Shoud be simple but doesn't work.

Sub fCheck_BP_BQ()
Dim cBR As Integer
finalrow9 = Cells(65536, 57).End(xlUp).Row
For ai = 2 To finalrow9
cBR = Cells(ai, 70).Value
If cBR > 40 Then Cells(ai, 78).Value = "No information in DCR path"
Next ai
End Sub

I have existing data and I need to set cBR to scroll from bottom up through each loop as the existing data in cell (row ai, column 70).

I tried to just type:-

if cells(ai,70).value > 40 then Cells(ai, 78).Value = "No information in DCR path"

but that errored as well.

Any ideas, Please help. Thanks

Please help I am stumped, but it should be so simple.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Not sure why you need to check backward through the range.

Does this work for you:

Code:
Sub test()
Dim finalrow9 As Long
finalrow9 = Range("BE" & Rows.Count).End(xlUp).Row
With Range("BR1:BR" & finalrow9)
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:=">40"
    Range("BZ2:BZ" & finalrow9) = "No Information in DCR Path"
    .AutoFilter
End With
End Sub
 

Mausten

New Member
Joined
May 14, 2007
Messages
15
Thanks

I'll copy this into my code and check it gives me what I am after - it looks like it should work.

Thanks again, One more thing not relevant to the subject heading.

What is the most efficient way of removing entire rows based on a cell value?

i.e. If cells(i,57).value = "?" then select all columns to the right and to the left of row i and delete them. I have tried using entirerow but can not get it to work.

Thanks.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Re: Thanks

What is the most efficient way of removing entire rows based on a cell value?

i.e. If cells(i,57).value = "?" then select all columns to the right and to the left of row i and delete them. I have tried using entirerow but can not get it to work.
You should be able to use EntireRow and in fact I would use almost the exact code HOTPEPPER has suggested for the previous question only change
Code:
Range("BZ2:BZ" & finalrow9) = "No Information in DCR Path
to
Code:
Rows("2:" & finalrow9).EntireRow.Delete
Of course you would also need to change Criteria1:=">40" to whatever value you want to delete the rows on.
 

Mausten

New Member
Joined
May 14, 2007
Messages
15

ADVERTISEMENT

Thanks - another one if you don't mind ?

Thanks for all of your help.

I have another quick question though, - Why does this code keep displaying an error? - Autofill method of range class failed.

Sub copy_autofill()

Dim finalrow16 As Long

finalrow16 = Range("BB" & Rows.Count).End(xlUp).Row
'counts rows in column BB and then sets finalrow16

With Range("bj2:bn" & finalrow16)
.AutoFill Destination:=Range("bj2:bn" & finalrow16)
End With
Application.CalculateFull
End Sub

Any help much appreciated. Thanks,
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Re: Thanks - another one if you don't mind ?

Thanks for all of your help.

I have another quick question though, - Why does this code keep displaying an error? - Autofill method of range class failed.

Sub copy_autofill()

Dim finalrow16 As Long

finalrow16 = Range("BB" & Rows.Count).End(xlUp).Row
'counts rows in column BB and then sets finalrow16

With Range("bj2:bn" & finalrow16)
.AutoFill Destination:=Range("bj2:bn" & finalrow16)
End With
Application.CalculateFull
End Sub

Any help much appreciated. Thanks,
Can you
1. Explain in words what your code is supposed to do?
2. Tells us what is already in the range BJ2:BN & finalrow16?
3. Tell us what is in column BB?
 

Mausten

New Member
Joined
May 14, 2007
Messages
15

ADVERTISEMENT

OK here goes

OK here goes;

1. Explain in words what your code is supposed to do?

ANS. The code is supposed to grab the first row of each of the 5 columns and copy this down to the last row set by the number of strings in a list in column BB. e.g. 300 strings in the list - finalrow16 = 300

I need to do this because everytime I sub total the column BB string list - at each change in BB - sum in another column some cells in columns BJ - BN display REF. So i copy the top formulas to the rest of the cells to the last row and the re-calculate all - update the formuals using ctrl, alt and f9. Let me know if you need anything more.

2. Tells us what is already in the range BJ2:BN & finalrow16?

ANS. 5 columns of formulas (Do I need to remove these first and then use .autofill ?)

3. Tell us what is in column BB?

ANS. Strings listing down to the number I want to be a final row. If you IM me an E-mail address I can post you a screen shot.

Thanks for any help.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Re: OK here goes

OK here goes;

1. Explain in words what your code is supposed to do?

ANS. The code is supposed to grab the first row of each of the 5 columns and copy this down to the last row set by the number of strings in a list in column BB. e.g. 300 strings in the list - finalrow16 = 300

I need to do this because everytime I sub total the column BB string list - at each change in BB - sum in another column some cells in columns BJ - BN display REF. So i copy the top formulas to the rest of the cells to the last row and the re-calculate all - update the formuals using ctrl, alt and f9. Let me know if you need anything more.

2. Tells us what is already in the range BJ2:BN & finalrow16?

ANS. 5 columns of formulas (Do I need to remove these first and then use .autofill ?)

3. Tell us what is in column BB?

ANS. Strings listing down to the number I want to be a final row. If you IM me an E-mail address I can post you a screen shot.

Thanks for any help.
See if this does what you want:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> copy_autofill()
    <SPAN style="color:#00007F">Dim</SPAN> finalrow16 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    finalrow16 = Range("BB" & Rows.Count).End(xlUp).Row
    <SPAN style="color:#007F00">'counts rows in column BB and then sets finalrow16</SPAN>
    
    <SPAN style="color:#00007F">With</SPAN> Range("bj2:bn2")
        .AutoFill Destination:=Range("bj2:bn" & finalrow16)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Application.CalculateFull
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

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
Top