Some trouble assigning a string to a variable

H

Howard

The code checks a variable length column whose cells are a link to another column of check boxes. A checked box returns a TRUE to this column which is rngTF, otherwise it is FALSE.

The For Each c In rngTF first checks if more than one TRUE is in the column and offers up a MsgBox if more than one TRUE.

If the column only has one TRUE I want to set the range variable hFill to the value four columns offset from the TRUE in C column, which is column G.

The Msgbox to display the value of hFill displays blank.

Thanks.
Howard


Option Explicit

Sub FillColH()
Dim c As Range, i As Long
Dim rngTF As Range '/ Column C Studies TRUE/FALSE's
Dim lrTF As Long '/ Last row in column C Studies
Dim Eitem As Variant '/ The drop down value in Studies E1
Dim hFill As String '/ the value four columns offset
'/ from TRUE value in rngTF (Column C Studies sheet)

With Sheets("Studies")
lrTF = .Cells(.Rows.Count, "C").End(xlUp).Row
Set rngTF = .Range("C5:C" & lrTF)
'Application.Goto rngTF 'don't need to select the range
End With

'/ set drop down value to variable Eitem
Eitem = Range("E1").Value

'/ Test Studies column C for more than one TRUE
'/ More than one checked box in column D
i = 0
For Each c In rngTF
If c = True Then
i = i + 1
Else
If i > 1 Then
MsgBox "There is more than ONE box checked in column D" & vbCr & _
" Review the boxes checked and check only one.", vbOKCancel, "Boxer Boxer"
Exit Sub
Else
hFill = c.Offset(, 4).Value ' hFill should be RT6
End If
End If
Next
MsgBox hFill
i = 0
End Sub
 
A

Auric__

Howard said:
The code checks a variable length column whose cells are a link to
another column of check boxes. A checked box returns a TRUE to this
column which is rngTF, otherwise it is FALSE.
Click to expand...

Out of curiosity, why checkboxes? If only one item of a group is allowed to
be selected, then using option (a.k.a. radio) buttons makes more sense to
me.
The For Each c In rngTF first checks if more than one TRUE is in the
column and offers up a MsgBox if more than one TRUE.

If the column only has one TRUE I want to set the range variable hFill
to the value four columns offset from the TRUE in C column, which is
column G.

The Msgbox to display the value of hFill displays blank.
Click to expand...

Replace the For-Each loop with this:

For Each c In rngTF
If c.Value = True Then
If i <> 0 Then
MsgBox "There is more than ONE box checked in column D" & vbCr & _
"Review the boxes checked and check only one.", vbOKOnly, _
"Boxer Boxer"
Else
i = -1
hFill = c.Offset(, 4).Value ' hFill should be RT6
End If
End If
Next
 
H

Howard

Howard wrote:








Out of curiosity, why checkboxes? If only one item of a group is allowed to

be selected, then using option (a.k.a. radio) buttons makes more sense to

me.









Replace the For-Each loop with this:



For Each c In rngTF

If c.Value = True Then

If i <> 0 Then

MsgBox "There is more than ONE box checked in column D" & vbCr & _

"Review the boxes checked and check only one.", vbOKOnly, _

"Boxer Boxer"

Else

i = -1

hFill = c.Offset(, 4).Value ' hFill should be RT6

End If

End If

Next
(When all else fails, play dead.)
Click to expand...


I was just coming back to my post to say I figured it out.
Here is my solution, which seems pretty close to what you offered, Auric__.

Do you see anything too off base with my code?

The checkboxes are the OP choice on an already configured & designed workbook.
I did insist on the removal of a ton of merged cells and hidden rows and columns. The workbook had soooo many array formulas that just to click from one cell to another took about 2 seconds.

I got a much much cleaner book back to work on and the check boxes were a new part of the clean book. I work very little with check boxes or radio buttons therefore I had no credible thought to do anything else.

I'll probably leave as is unless you could offer up a simpler method, (one that even I can follow)


i = 0
For Each c In rngTF
If i > 1 Then
MsgBox "There is more than ONE box checked in column D" & vbCr & _
" Review the boxes checked and check only one.", vbOKCancel, "Boxer Boxer"
Exit Sub
End If

If c = True Then
i = i + 1
hFill = c.Offset(, 4)
End If
Next
i = 0


My am puzzling on how to make this work now that I got my variable assigned:

For Each c In colA
If c = Eitem And c.Offset(, 2) = hFill Then
c.Offset(, 4).Copy Sheets("Studies").Range("H" & Rows.Count).End(xlUp)(2)
End If
Next


Eitem hFill Offset_Value
Offset_Value
Offset_Value
Offset_Value
Offset_Value
Offset_Value
Eitem2 hFill2 Offset_Value2
Offset_Value2
Offset_Value2
Offset_Value2
Offset_Value2
Offset_Value2

When the Eitem and hFill find a match then return the 6 Offset_Value to range H like the offset copy line says. The 6 offset_Values may be 10, 2, 9 etc.

As is, it does return only the first Offset_Value, correctly to the right range.

I can't figure out a resize method to copy the variable lines down to the next Eitem2 and hFill2.

Don't know how well this will post, may come across as a mess.

Howard
 
C

Claus Busch

Hi Howard,

Am Sat, 7 Dec 2013 00:30:22 -0800 (PST) schrieb Howard:

I have another suggestion:

'/ Test Studies column C for more than one TRUE
'/ More than one checked box in column D
If WorksheetFunction.CountIf(rngTF, True) > 1 Then
MsgBox "There is more than ONE box checked in column D" & vbCr & _
" Review the boxes checked and check only one.",
vbOKCancel, "Boxer Boxer"
Exit Sub
End If
Set c = rngTF.Find(True, LookIn:=xlValues)
If Not c Is Nothing Then
hFill = c.Offset(, 4).Value ' hFill should be RT6
End If

MsgBox hFill
My am puzzling on how to make this work now that I got my variable assigned:

For Each c In colA
If c = Eitem And c.Offset(, 2) = hFill Then
c.Offset(, 4).Copy Sheets("Studies").Range("H" & Rows.Count).End(xlUp)(2)
End If
Next
Click to expand...

I don't really understand. c.offset(,2) = column C. In column C is TRUE
or FALSE. How could be hFill in this column? Is hFill and/or EItem
unique?


Regards
Claus B.
 
H

Howard

Hi Howard,



Am Sat, 7 Dec 2013 00:30:22 -0800 (PST) schrieb Howard:



I have another suggestion:



'/ Test Studies column C for more than one TRUE

'/ More than one checked box in column D

If WorksheetFunction.CountIf(rngTF, True) > 1 Then

MsgBox "There is more than ONE box checked in column D" & vbCr & _

" Review the boxes checked and check only one.",

vbOKCancel, "Boxer Boxer"

Exit Sub

End If

Set c = rngTF.Find(True, LookIn:=xlValues)

If Not c Is Nothing Then

hFill = c.Offset(, 4).Value ' hFill should be RT6

End If



MsgBox hFill










I don't really understand. c.offset(,2) = column C. In column C is TRUE

or FALSE. How could be hFill in this column? Is hFill and/or EItem

unique?





Regards

Claus B.
Click to expand...

Your time permitting perhaps you could look at the workbook.

https://www.dropbox.com/s/awjxe9zxgvvyx40/Copy of Shopper Studies Test2 vLHK Drop Box.xlsm

On the Studies sheet:

Box in cell D5 is checked.
Eitem = cell E1 (2013-Wireline)
hFill = cell G5 (RT1)

On the Data sheet:

A2 = Eitem
C2 = hFill
E2:E10 to be copied to Sturies sheet F5 and down. (Orange colored range)

Note the variable numbers of rows between A & C column entries.
So the resize row number has to change to reflect these changing row numbers.

Thanks.
Howard
 
C

Claus Busch

Hi Howard,

Am Sat, 7 Dec 2013 02:25:18 -0800 (PST) schrieb Howard:
https://www.dropbox.com/s/awjxe9zxgvvyx40/Copy of Shopper Studies Test2 vLHK Drop Box.xlsm

On the Studies sheet:

Box in cell D5 is checked.
Eitem = cell E1 (2013-Wireline)
hFill = cell G5 (RT1)

On the Data sheet:

A2 = Eitem
C2 = hFill
E2:E10 to be copied to Sturies sheet F5 and down. (Orange colored range)

Note the variable numbers of rows between A & C column entries.
So the resize row number has to change to reflect these changing row numbers.
Click to expand...

for sheet Data try:

With Sheets("Data")
LRow = .Cells(.Rows.Count, 4).End(xlUp).Row
With .Range("A1:A" & LRow)
Set c = .Find(Eitem, after:=.Range("A1"), LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
If c.Offset(, 2) = hFill Then
LCell = WorksheetFunction.Match("*", .Range(.Cells(c.Row +
1, 1), _
.Cells(c.Row + 20, 1)), 0)
varOut = .Range(.Cells(c.Row, "E"), .Cells(LCell + c.Row -
1, "E"))
Sheets("Studies").Cells(Rows.Count, "F").End(xlUp).Offset(1,
0) _
.Resize(rowsize:=UBound(varOut)) = varOut
Exit Do
Else
Set c = .FindNext(c)
End If
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
End With


Regards
Claus B.
 
H

Howard

Hi again,



Am Sat, 7 Dec 2013 12:13:18 +0100 schrieb Claus Busch:






please look here:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

for "Shopper Studies Test2"





Regards

Claus B.
Click to expand...


Hi Claus,

I downloaded the workbook and tried you revised code.

It doesn't run for some reason. I Moved it from Modul 1 to Module 1 and still no action.

I checked two boxes and ran the new code and it does not give the msgbox warning about more than one box checked. Seems quite dormant.

I must be doing something wrong as you code is ALWAYS bullet proof.

Anything I'm overlooking that you can think of?

Howard
 
C

Claus Busch

Hi Howard,

Am Sat, 7 Dec 2013 07:29:05 -0800 (PST) schrieb Howard:
It doesn't run for some reason. I Moved it from Modul 1 to Module 1 and still no action.

I checked two boxes and ran the new code and it does not give the msgbox warning about more than one box checked. Seems quite dormant.

I must be doing something wrong as you code is ALWAYS bullet proof.
Click to expand...

no, I tested now again and everything works. The messagebox as well as
the data copy.
I did little changes and uploaded the file 15 minutes ago. Do you have
the newest version?
Did you run "FillColH2"?
Look into the VBA-Editor if the code is stopped.


Regards
Claus B.
 
H

Howard

Hi Howard,



Am Sat, 7 Dec 2013 07:29:05 -0800 (PST) schrieb Howard:






no, I tested now again and everything works. The messagebox as well as

the data copy.

I did little changes and uploaded the file 15 minutes ago. Do you have

the newest version?

Did you run "FillColH2"?

Look into the VBA-Editor if the code is stopped.





Regards

Claus B.
Click to expand...


Sub FillColH2()

This is the name of the code I am trying to run, and it in Modul1.
And there is a Module1 right below it.

No errors or anything, just does not run, either from the VB Editor clicking the Run Sub arrow or from the sheet with alt + f8 select FillColH2 > Run, or assigning it to the Box Button on the sheet and clicking the button.

I have saved and closed, reopened, still no run.

I'm at a loss??

Howard
 
C

Claus Busch

Hi Howard,

Am Sat, 7 Dec 2013 08:39:05 -0800 (PST) schrieb Howard:
No errors or anything, just does not run, either from the VB Editor clicking the Run Sub arrow or from the sheet with alt + f8 select FillColH2 > Run, or assigning it to the Box Button on the sheet and clicking the button.
Click to expand...

did you uncheck all checkboxes?


Regards
Claus B.
 
H

Howard

Hi Howard,



Am Sat, 7 Dec 2013 08:39:05 -0800 (PST) schrieb Howard:






did you uncheck all checkboxes?





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...



Okay, I just tried the code again with two boxes checked and I got the msgbox warning about more than 1 box checked. So, it is running but doing nothing past checking for more than 1 checked box.

Not sure what that means.

When you (at your end) run it, does it fill F5 and down with some lines of data?
I see in the code that is supposed to copy to F5 (don't understand most of the code in the With Sheets("Data")...)

Howard

Howard
 
C

Claus Busch

Hi Howard,

Am Sat, 7 Dec 2013 10:19:41 -0800 (PST) schrieb Howard:
Okay, I just tried the code again with two boxes checked and I got the msgbox warning about more than 1 box checked. So, it is running but doing nothing past checking for more than 1 checked box.

Not sure what that means.

When you (at your end) run it, does it fill F5 and down with some lines of data?
I see in the code that is supposed to copy to F5 (don't understand most of the code in the With Sheets("Data")...)
Click to expand...

yes, It does.
Is the last code line before End Sub
Application.Screenupdating = True?
Please upload the file that I can check it.


Regards
Claus B.
 
H

Howard

Hi Howard,



Am Sat, 7 Dec 2013 10:19:41 -0800 (PST) schrieb Howard:







yes, It does.

Is the last code line before End Sub

Application.Screenupdating = True?

Please upload the file that I can check it.





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...


Here is the elusive code copied out of Modul1. Looks good to me, but alas.
And thanks for your time and efforts.

Howard


Option Explicit

Sub FillColH2()
Dim c As Range, i As Long
Dim rngTF As Range '/ Column C Studies TRUE/FALSE's
Dim colA As Range '/ Column A range sheet Data
Dim colC As Range '/ Column C range sheet Data
Dim lrTF As Long '/ Last row in column C Studies
Dim lr As Long '/ Last row in column A & C Data sheet
Dim Eitem As String '/ The drop down value in Studies E1
Dim hFill As String '/ the value four columns offset
'/ from TRUE value in rngTF (Column C Studies sheet)
Dim LRow As Long '/Last row in sheet Data
Dim firstaddress As String
Dim LCell As Long
Dim varOut As Variant

Application.ScreenUpdating = False
With Sheets("Studies")
lrTF = .Cells(.Rows.Count, "C").End(xlUp).Row
Set rngTF = .Range("C5:C" & lrTF)
'Application.Goto rngTF 'don't need to select the range


'/ set drop down value to variable Eitem
Eitem = .Range("E1").Text

'/ Test Studies column C for more than one TRUE
'/ More than one checked box in column D
'/ Sets col G value to hfill

If WorksheetFunction.CountIf(rngTF, True) > 1 Then
MsgBox "There is more than ONE box checked in column D" & vbCr & _
" xx Review the boxes checked and check only one.", vbOKCancel, "Boxer Boxer"
Exit Sub
End If

For Each c In rngTF
If c = True Then
hFill = c.Offset(, 4)
Exit For
End If
Next
End With

With Sheets("Data")
LRow = .Cells(.Rows.Count, 4).End(xlUp).Row
With .Range("A1:A" & LRow)
Set c = .Find(Eitem, after:=.Range("A1"), LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
If c.Offset(, 2) = hFill Then
'Lcell is the first row with a value after c
LCell = WorksheetFunction.Match("*", .Range(.Cells(c.Row + 1, 1), _
.Cells(c.Row + 20, 1)), 0)
varOut = .Range(.Cells(c.Row, "E"), .Cells(LCell + c.Row - 1, "E"))
Sheets("Studies").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) _
.Resize(rowsize:=UBound(varOut)) = varOut
Exit Do
Else
Set c = .FindNext(c)
End If
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
End With
Application.ScreenUpdating = True
End Sub
 
C

Claus Busch

Hi Howard,

Am Sat, 7 Dec 2013 12:52:18 -0800 (PST) schrieb Howard:
Here is the elusive code copied out of Modul1. Looks good to me, but alas.
And thanks for your time and efforts.
Click to expand...

I tested it and it works fine for me. Where do you want the data from
"Data" column E? The code now will paste the data in the first empty row
of sheet "Studies" column F. That is now F42 downwards.

I changed the checking for checked boxes to get a msgbox if nothing is
checked:

If WorksheetFunction.CountIf(rngTF, True) > 1 Then
MsgBox "There is more than ONE box checked in column D" & vbCr & _
" Review the boxes checked and check only one.", vbOKCancel,
"Boxer Boxer"
Exit Sub
ElseIf WorksheetFunction.CountIf(rngTF, True) = 0 Then
MsgBox "There is no box checked in column D" & vbCr & _
" Please check one.", vbOKCancel, "Boxer Boxer"
Exit Sub
End If


Regards
Claus B.
 
H

Howard

Hi Howard,



Am Sat, 7 Dec 2013 12:52:18 -0800 (PST) schrieb Howard:







I tested it and it works fine for me. Where do you want the data from

"Data" column E? The code now will paste the data in the first empty row

of sheet "Studies" column F. That is now F42 downwards.



I changed the checking for checked boxes to get a msgbox if nothing is

checked:



If WorksheetFunction.CountIf(rngTF, True) > 1 Then

MsgBox "There is more than ONE box checked in column D" & vbCr & _

" Review the boxes checked and check only one.", vbOKCancel,

"Boxer Boxer"

Exit Sub

ElseIf WorksheetFunction.CountIf(rngTF, True) = 0 Then

MsgBox "There is no box checked in column D" & vbCr & _

" Please check one.", vbOKCancel, "Boxer Boxer"

Exit Sub

End If





Regards

Claus B.

--
Click to expand...

That addition makes very good sense.

The code did run and did error out on this line.

Sheets("Studies").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) _
.Resize(rowsize:=UBound(varOut)) = varOut

Howard
 
C

Claus Busch

Hi again,

Am Sat, 7 Dec 2013 13:48:33 -0800 (PST) schrieb Howard:
The code did run and did error out on this line.

Sheets("Studies").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) _
.Resize(rowsize:=UBound(varOut)) = varOut
Click to expand...

I can't reproduce it. Here it works without any error.

For "Media Influence"there are more than 20 empty rows and the code line
must be changed to (20 to 30):
'Lcell is the first row with a value after c
LCell = WorksheetFunction.Match("*", .Range(.Cells(c.Row + 1, 1), _
.Cells(c.Row + 30, 1)), 0)


Regards
Claus B.
 
C

Claus Busch

Hi again,

Am Sat, 7 Dec 2013 23:07:25 +0100 schrieb Claus Busch:
For "Media Influence"there are more than 20 empty rows and the code line
must be changed to (20 to 30):
'Lcell is the first row with a value after c
LCell = WorksheetFunction.Match("*", .Range(.Cells(c.Row + 1, 1), _
.Cells(c.Row + 30, 1)), 0)
Click to expand...

sometimes there are no empty cells (only one row with data). Therefore I
made some changes:

Sub FillColH2()
Dim c As Range, i As Long
Dim rngTF As Range '/ Column C Studies TRUE/FALSE's
Dim colA As Range '/ Column A range sheet Data
Dim colC As Range '/ Column C range sheet Data
Dim lrTF As Long '/ Last row in column C Studies
Dim lr As Long '/ Last row in column A & C Data sheet
Dim EItem As String '/ The drop down value in Studies E1
Dim hFill As String '/ the value four columns offset
'/ from TRUE value in rngTF (Column C Studies sheet)
Dim LRow As Long '/Last row in sheet Data
Dim firstaddress As String
Dim LCell As Long
Dim varOut As Variant

Application.ScreenUpdating = False
With Sheets("Studies")
lrTF = .Cells(.Rows.Count, "C").End(xlUp).Row
Set rngTF = .Range("C5:C" & lrTF)
'Application.Goto rngTF 'don't need to select the range


'/ set drop down value to variable Eitem
EItem = .Range("E1").Text

'/ Test Studies column C for more than one TRUE
'/ More than one checked box in column D
'/ Sets col G value to hfill

If WorksheetFunction.CountIf(rngTF, True) > 1 Then
MsgBox "There is more than ONE box checked in column D" & vbCr & _
" Review the boxes checked and check only one.", vbOKCancel,
"Boxer Boxer"
Exit Sub
ElseIf WorksheetFunction.CountIf(rngTF, True) = 0 Then
MsgBox "There is no box checked in column D" & vbCr & _
" Please check one.", vbOKCancel, "Boxer Boxer"
Exit Sub
End If

For Each c In rngTF
If c = True Then
hFill = c.Offset(, 4)
Exit For
End If
Next
End With

With Sheets("Data")
LRow = .Cells(.Rows.Count, 4).End(xlUp).Row
With .Range("A1:A" & LRow)
Set c = .Find(EItem, after:=.Range("A1"), LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
If c.Offset(, 2) = hFill Then
'If there are empty cells down c
If IsEmpty(c.Offset(1, 0)) Then
'Lcell is the first row with a value after c
LCell = WorksheetFunction.Match("*", .Range(.Cells _
(c.Row + 1, 1), .Cells(c.Row + 30, 1)), 0)
'Array with data from column E
varOut = .Range(.Cells(c.Row, "E"), .Cells(LCell + c.Row
- 1, "E"))
Sheets("Studies").Cells(Rows.Count,
"F").End(xlUp).Offset(1, 0) _
.Resize(rowsize:=UBound(varOut)) = varOut
Exit Do
'If there are no empty cells down c
Else
Sheets("Studies").Cells(Rows.Count,
"F").End(xlUp).Offset(1, 0) _
= .Range("E" & c.Row)
Exit Do
End If
Else
Set c = .FindNext(c)
End If
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
 
H

Howard

Hi again,



Am Sat, 7 Dec 2013 23:07:25 +0100 schrieb Claus Busch:










sometimes there are no empty cells (only one row with data). Therefore I

made some changes:



Sub FillColH2()

Dim c As Range, i As Long

Dim rngTF As Range '/ Column C Studies TRUE/FALSE's

Dim colA As Range '/ Column A range sheet Data

Dim colC As Range '/ Column C range sheet Data

Dim lrTF As Long '/ Last row in column C Studies

Dim lr As Long '/ Last row in column A & C Data sheet

Dim EItem As String '/ The drop down value in Studies E1

Dim hFill As String '/ the value four columns offset

'/ from TRUE value in rngTF (Column C Studies sheet)

Dim LRow As Long '/Last row in sheet Data

Dim firstaddress As String

Dim LCell As Long

Dim varOut As Variant



Application.ScreenUpdating = False

With Sheets("Studies")

lrTF = .Cells(.Rows.Count, "C").End(xlUp).Row

Set rngTF = .Range("C5:C" & lrTF)

'Application.Goto rngTF 'don't need to select the range





'/ set drop down value to variable Eitem

EItem = .Range("E1").Text



'/ Test Studies column C for more than one TRUE

'/ More than one checked box in column D

'/ Sets col G value to hfill



If WorksheetFunction.CountIf(rngTF, True) > 1 Then

MsgBox "There is more than ONE box checked in column D" & vbCr & _

" Review the boxes checked and check only one.", vbOKCancel,

"Boxer Boxer"

Exit Sub

ElseIf WorksheetFunction.CountIf(rngTF, True) = 0 Then

MsgBox "There is no box checked in column D" & vbCr & _

" Please check one.", vbOKCancel, "Boxer Boxer"

Exit Sub

End If



For Each c In rngTF

If c = True Then

hFill = c.Offset(, 4)

Exit For

End If

Next

End With



With Sheets("Data")

LRow = .Cells(.Rows.Count, 4).End(xlUp).Row

With .Range("A1:A" & LRow)

Set c = .Find(EItem, after:=.Range("A1"), LookIn:=xlValues)

If Not c Is Nothing Then

firstaddress = c.Address

Do

If c.Offset(, 2) = hFill Then

'If there are empty cells down c

If IsEmpty(c.Offset(1, 0)) Then

'Lcell is the first row with a value after c

LCell = WorksheetFunction.Match("*", .Range(.Cells _

(c.Row + 1, 1), .Cells(c.Row + 30, 1)), 0)

'Array with data from column E

varOut = .Range(.Cells(c.Row, "E"), .Cells(LCell + c.Row

- 1, "E"))

Sheets("Studies").Cells(Rows.Count,

"F").End(xlUp).Offset(1, 0) _

.Resize(rowsize:=UBound(varOut)) = varOut

Exit Do

'If there are no empty cells down c

Else

Sheets("Studies").Cells(Rows.Count,

"F").End(xlUp).Offset(1, 0) _

= .Range("E" & c.Row)

Exit Do

End If

Else

Set c = .FindNext(c)

End If

Loop While Not c Is Nothing And c.Address <> firstaddress

End If

End With

End With

Application.ScreenUpdating = True

End Sub





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...


I am so sorry for being such a jinx!
The new revised code is not working past the check boxes for none or more than one.

Here is my book, perhaps I have something screwed up within it.
I sure appreciate your help and sorry for being such a pain in the @#$$.

Howard

https://www.dropbox.com/s/4lcdl9o5d7h57nk/Shopper Studies Test2 FillColH3 Drop Box.xlsm
 
C

Claus Busch

Hi Howard,

Am Sat, 7 Dec 2013 14:47:28 -0800 (PST) schrieb Howard:
https://www.dropbox.com/s/4lcdl9o5d7h57nk/Shopper Studies Test2 FillColH3 Drop Box.xlsm
Click to expand...

I tested the workbook and everything is fine.
In all your posts you wrote that you wanted the data from sheet "Data"
column E in sheet "Studies" column F in the first empty row.
With all checkboxes I tried the data is written in F42 downwards.
Do you looking for the data in column H (H5 downwards) because one of
the variables is called hFill?
Then you only have to change the "F" to a "H" in
Sheets("Studies").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) _
.Resize(rowsize:=UBound(varOut)) = varOut

If that is not the solution then look how c, LCell and varOut change
when you step through the code with F8

I go to bed now. Until tomorrow.


Regards
Claus B.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove Identical words 0
Use a variable in a ShapeRange.Increment 0
Working outlook via vba code with xlsx - error 0
Word macro that can call cells out of Excel for a find and replace 0
Match style 0
VBA Coding Help for Beginner 0
Object variable or with Block variable not set 9
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0

Top