VBA XL 2010. Loop, define set of cells in a named column

[Ambre]

Hello everyone,

I am a begginer in VBA and had no real training so I am learning on my own by reading books and exploring forums.
I went through many posts in several forum and Microsoft websites in order to write the code I need but unsuccessfully.

What I am trying to do:
If the cells in column 1 (starting from row 15 and going down until an empty cell) are not empty, put "No" in cells in the column named "Savings" (starting from row 15). I do not want to write the column number because if the number of the column changes, the macro will not run anymore.

What I have written so far :

Option Explicit

Public Sub NoInCells()

Dim CelCount As Integer
CelCount = 15

Range("A15").Select
Do Until IsEmpty(ActiveCell)
'Find the column to write No
Dim column_criteria As Integer
For column_criteria = 1 To 200
If Worksheets("sheet1").Cells(11, column_criteria) = "Savings" Then
????(how to define the cells of the column named Savings)????.FormulaR1C1 = "No"
End If

ActiveCell.Offset(1, 0).Select

Next

CelCount = CelCount + 1

Loop

End Sub



Maybe I have some redundancies here.

Many thanks for your help.

I have other issues with other macros, should I create a post for each of them or write everything in the same?


Ambre
Excel 2010
Windows XP

 

[Comfy]

So if Cell A17 was blank you would want Cell 15, 16 and 17 in the Savings column to say "no" and the macro finish there? and not continue through the rest of the column until it reached the last row in the column.

 

[Ambre]

I should not have any blank rows in the file and the column A should never be empty, then if the macro finishes at the first blank cell it should be ok because it means it went through all the rows i want. It was easier for me to do it this way, but if you can help me in order to go to the last row in the column instead of the first blank cell, would be great

 

[Comfy]

Just seen your reply, will change the macro.

 

[Comfy]

This will find all the blank cells from row 15 to the last row in column A and will put "No" in the column headed "Savings"

Option Explicit
Public Sub NoInCells()


Dim SaveCol As Range
Dim lRow As Long


Set SaveCol = Rows(11).Find(What:="Savings", LookIn:=xlValues, LookAt:=xlWhole)
If SaveCol Is Nothing Then MsgBox "Savings Header not Found": Exit Sub


lRow = Cells(Rows.Count, 1).End(xlUp).Row


Range(Cells(15, 1), Cells(lRow, 1)).SpecialCells(xlCellTypeBlanks).Offset(0, SaveCol.Column - 1).Value = "No"


End Sub

 

[Ambre]

Thanks Comfy, but this is not what I was trying to do. My bad, my previous message was not clear at all actually!
When I said "I should not have", I meant I do not have blank cells in my file (none in the middle of my data) so running the macro until the first blank cell or to the last row is the same in my case. What I would like to do is to put No in every cell (column savings), when the cell is not empty in column A (and none is empty) so I should have No in every single row until the last one.

(to explain the entire thing: in my workbook, first i have a macro that imports rows to this sheet from another one, then I have a macro that generates a Serial number to each row in column A, and then this macro should put No is the column Savings (as default).
Hope this helps

 

[Comfy]

In that case you want this:

Option Explicit
Public Sub NoInCells()




Dim SaveCol As Range
Dim lRow As Long




Set SaveCol = Rows(11).Find(What:="Savings", LookIn:=xlValues, LookAt:=xlWhole)
If SaveCol Is Nothing Then MsgBox "Savings Header not Found": Exit Sub




lRow = Cells(Rows.Count, 1).End(xlUp).Row




Range(Cells(15, SaveCol.Column), Cells(lRow, SaveCol.Column)).Value = "No"




End Sub

 

[Ambre]

This works perfectly! thank you very much

 

[Ambre]

Comfy,

This is about another macro. It generates a Serial number to each new row added in column A.
This works fine until the last row in the sheet.
Last serial number does not stop when it should. For eg.
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Serial number[/TD]
[TD].........[/TD]
[/TR]
[TR]
[TD]169[/TD]
[TD]write 128 and then continues until 169[/TD]
[/TR]
[TR]
[TD]127[/TD]
[TD]ok[/TD]
[/TR]
[TR]
[TD]126[/TD]
[TD]ok[/TD]
[/TR]
[TR]
[TD]125[/TD]
[TD]last serial number before addition of new rows[/TD]
[/TR]
</TBODY>[/TABLE]
I cannot find my mistake. I think I have to write Exit something maybe but not sure.

[TABLE="width: 500"]
<TBODY>[TR]
[TD]
'Find the last serial number and assign following serial number to the new line added

Dim rng As Range
Dim dblMax As Double
Set rng = Range("A1", Range("A65536").End(xlUp))
dblMax = Application.WorksheetFunction.Max(rng)

Dim number As Double
number = dblMax + 1
Sheets("Purchasing Plan").Select
Range("A15") = number
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Comfy]

Have you posted all your code? As I cannot ascertain what it is you want to do from your question and example.

This for example,[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]"'Find the last serial number and assign following serial number to the new line added"

Where and how is the new line added? Manually, through other code?
[/TD]
[/TR]
</tbody>[/TABLE]
What you have posted only shows that you are finding the largest number in column A, adding 1 too it and then putting that number in A15 on the purchasing plan sheet, of which you make no mention in your example.