 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2008Adding a WHERE statement
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. 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. > I recorded a basic macro and want to add a WHERE statement in the body and > need some help. I recorded [quoted text clipped - 10 lines] > 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. 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. > Never have come across a Where statement. You might be able to use an If > statement or a While statement. [quoted text clipped - 26 lines] > > 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. 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 > 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 [quoted text clipped - 34 lines] > > > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.