Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Fill Blank rows

  Asked By: Molly    Date: Sep 01    Category: MS Office    Views: 677
  

Column E contains some blank cells. Can any one please tell me how to i fill
these cells. I try to first run a loop in Column E but i dont want to fill all
the rows unpto end in that column with a value. I just want to fill the values
until column A has values. I mean i want to fill column E blank spaces with
value "OX" untill the column A is end.
Basically i create a new excel file from two other excel files. But some rows in
column E in new excel file has blank values which i need to be fill with value
"OX". Upto what point i will be fill the values, i want to check the column A
and where the column A starting blank rows i want to stop filling in column E.
As column A contains no blank cell in between two rows.


For Each rngCell In xlWS.Range("A:A")
If rngCell.Value = vbNullString Then
Exit For
Else
Next
SOME PART OF THE PROGRAM
------------------------
Private Sub processexcelfiles(x As String, Optional useValue As Boolean = True)
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim rngColumn As Excel.Range
Dim rngCell As Excel.Range
Dim strDestination As String
Dim mysheet As String
Const xlCenter = -4108
ProgressBar1.Value = 1
ProgressBar1.Value = 10
ProgressBar1.Value = 20
ProgressBar1.Value = 30
'strSource = strPath
If x > "" Then
' strDestination = strPath & "PIMS Payment File" & " " & Format(Date,
"MMDDYY") & ".xls"
strSource = x
strDestination = StripPath(x) & InputBox("Please Enter a Name for your
file") & ".XLS"
FileCopy strSource, strDestination
strSource = strDestination
strDestination = ""
Else
strSource = strXLSFile2
End If
strXLSFile2 = ""
Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open(strSource)
mysheet = xlApp.Worksheets(1).Name
Set xlWS = xlWb.Worksheets(mysheet)
xlWS.Range("A:A,B:B,D:D,E:E,F:F,G:G,H:H,L:L,M:M,N:N,O:O,P:P,Q:Q,S:S,T:T,U:U,V:V,\
W:W").Delete Shift:=xlToLeft
xlWS.Columns("A:A").Insert Shift:=xlToRight
xlWS.Columns("F:F").Cut
xlWS.Range("A1").Select
ActiveSheet.Paste
xlWS.Columns("A:H").EntireColumn.AutoFit
'Columns("A1:H1").HorizontalAlignment = xlCenter
xlWS.Range("A1").Value = "Account Number"
xlWS.Range("B1").Value = "Amount"
xlWS.Range("C1").Value = "Tran Code"
xlWS.Range("D1").Value = "Tran Date"
xlWS.Range("E1").Value = "Reference"
xlWS.Range("F1").Value = "Payment Type ID"
xlWS.Range("G1").Value = "Tran Source"
xlWS.Range("H1").Value = "Due Agency"
For Each rngCell In xlWS.Range("C:C")
If rngCell.Value = vbNullString Then
Exit For
Else
Next

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Guadalupe Rogers     Answered On: Sep 01

If you change your IF statement slightly, you can use your existing
logic:

IF IsEmpty rngCell.Value AND IsEmpty rngCell.Offset(0,4).Value Then

' DO stuff
Else
' Do nothing
End If

 
Answer #2    Answered By: Gustavo Taylor     Answered On: Sep 01

You can have a code like following

sub fill()

Range("a65000".end(xlup)).offset(0,4).select

do while activecell.row<>1
if activecell = "" then
activecell = "OX"
end if

loop

 
Answer #3    Answered By: Velma Adams     Answered On: Sep 01

Now, if you want to put check  marks in the cells  in col "e" if col "a"
aren't empty then you could try this:

'***********************************************
Sub Fill_Column()
Call ProcessData(True)
End Sub
Function ProcessData(Optional boolCheckMark As Boolean)
Dim rngEnd As Range
Dim CL As Range
Dim rngCl As Range
Const strVal As String = "OX"
Const strCompareCol As String = "A"
Const strFillCol As String = "E"

'get the last used cell  in column  "a"
Set rngEnd = Columns(strCompareCol).Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows)
For Each CL In Range([a1], rngEnd)
Set rngCl = Cells(CL.Row, Columns(strFillCol).Column)
If CL = "" Then
With rngCl
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Value = strVal
End With
ElseIf boolCheckMark Then
Call AddCheckMark(rngCl)
End If
Next CL
End Function

Function AddCheckMark(rngTarget As Range)
With rngTarget
.Font.Name = "Webdings"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Value = "a"
End With
End Function
'********************************************************

 
Didn't find what you were looking for? Find more on Fill Blank rows Or get search suggestion and latest updates.




Tagged:      

 
 
 
 

Related Post