Looping thru a range - storing IF not blank?

[Alex Coker]

Can anyone help with this task? I have never used VBA, but I am a
programmer.

I have a worksheet of survey results. It has five questions and allows for
five answers and comments. I want to consequtively report the comments to
the employee in a report. In other words, I have a range of comment cells,
some of which have content (most do not). I want to loop through the range
and when the comment cell has content, store it in another cell.

If you need more information, please ask.

Thanks,
Alex

 

[Dave Peterson]

Maybe something like:

Option Explicit
sub testme01()

dim myCommentCells as Range
dim oRow as long
dim myCell as range

with worksheets("sheet1")
set mycommentcells = .range("a1:a5,c3:c9,d5:d44")
end with

orow = 1
for each mycell in mycommentcells.cells
if trim(mycell.value) = "" then
'do nothing
else
worksheets("sheet2").cells(orow,"A").value = mycell.value
orow = orow + 1
end if
next mycell

end sub

 

[Guest]

A couple of possible solutions... Some easier than others.

1. instead of using code you could use could just use a filter and filter on
non blank cells. That is by far the easiest.

2. you can use a sub such as this

public sub ReportComments()
dim lngLastRow as long
dim rngTraverse as range
dim rngCopyTo as range

lnglastrow = sheets("Sheet1").range("A1").specialcells(xlLastCell).row
set rngTraverse = sheets("sheet1").range("B1")
set rngCopyTo = sheets("Sheet2").range("B1")

do while rngtraverse.row <= lngLastRow
if rngtraverse.value <> empty then
rngCopyTo.value = rngtraverse.value
set rngcopyto = rngcopyto.offset(1,0)
end if
set rngtraverse = rngtraverse.offset(1,0)
loop

set rngcopyto = nothing
set rnttraverse = nothing
end sub

or something like that...

Hope this helps...

 

[Alex Coker]

Great! I chose this example because I understood it better. Now that I can
retrieve the comments, I need to take it a couple of steps further. Each
employee has a worksheet unto themselves. I need to add logic to validate
the active worksheet and only pull comments for this individual. I am
standardizing the worksheet names like 'RonScorecard, DonScorecard, or
LisaScorecard. Then I can target the comments appropriately.

I hate to have to ask for this sort of help, so if you have a recommendation
on a good VBA book, let me know and I'll spend time getting up to speed on
this sort of thing.

Thanks for everyone's replies.

Alex

 

[Dave Peterson]

Same addresses in all the worksheets?

Option Explicit
Sub testme01a()

Dim myCommentCellsAddresses As String
Dim oRow As Long
Dim myCell As Range
Dim wks As Worksheet
Dim myRng As Range

myCommentCellsAddresses = "a1:a5:c3:c9,d5:d44"

oRow = 1
For Each wks In ActiveWorkbook.Worksheets
If LCase(wks.Name) Like LCase("*scorecard") Then
Set myRng = wks.Range(myCommentCellsAddresses)
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
'do nothing
Else
Worksheets("sheet2").Cells(oRow, "A").Value = myCell.Value
oRow = oRow + 1
End If
Next myCell
End If
Next wks

End Sub