WHERE is an SQL statement, so i don't think you will find it in excel or VBA
WHILE forms a conditional loop in excel and is part of most programming
languages
WHERE is reasonably similar to an if statement and a loop
the loop specifies the range you want to test, in this case everycell in
column b?
if will test each of those value
Dim iCount As Integer
Dim sCell As String
For iCount = 2 To 65536
scell = Range("B"&CStr(i)).Value
If cell < 2 Or scell = "" Then Range("B"&CStr(i)).Value = "1"
Next iCount
Will Loop through all of column b and replace any blank cells or cells with
values less than 2 with 1
"NEWER USER" wrote:
> I have an Excel table with data in Column A and may or may not have data in
> Column B. I f no data is present in Cloumn B, I want to fill the entire
> Column B with a value of "1". I don't know how many rows of data exist as it
> varies, so I was filling the whole column. I link this table to an Access
> database and perform update querries on the database. I suppose Excel uses
> the WHILE syntax where Access uses WHERE. My recorded macro places a value of
> "1" in EVERY cell instead of just the Null cells.
>
> "JLGWhiz" wrote:
>
> > Never have come across a Where statement. You might be able to use an If
> > statement or a While statement.
> >
> > exmpl: For Each cell In Range("B2:B500")
> > If cell < 2 Or cell = "" Then
> > 'Do Something or Not
> > End If
> > Next
> >
> > or exmpl2: While Range("B2:B500") < 2 Or Range("B2:B500") = ""
> > 'Do Something or Not
> > Wend
> >
> > If you would explain what you want to accomplish, somebody will probably be
> > able to help you.
> >
> > "NEWER USER" wrote:
> >
> > > I recorded a basic macro and want to add a WHERE statement in the body and
> > > need some help. I recorded
> > >
> > > Range("B2").Select
> > > ActiveCell.FormulaR1C1 = "1"
> > > Range("B2").Select
> > > Selection.Copy
> > > Range("B2:B65536").Select
> > > Application.CutCopyMode = False
> > > Selection.FillDown
> > > Range("A2").Select
> > >
> > > I want to fill Column B entirely WHERE any value LESS THAN 2 OR NULL. Any
> > > Help appreciated as to where and How to add statement.
|
Similar Threads
