right now this code exports to another sheet, and only exports the active
cell. I would like to export a given cell range in a row (A4:C4, F4,I4:Q4),
with a comfirmation based on F4 being "y" vs "n".

Sub ClickAdd()

Dim rngAvailable As Range, rngCell As Range, bolSuccess As Boolean

Set rngAvailable = ThisWorkbook.Worksheets("Sheet1").Range("B18:B40")
'Range Of Cells that needs to change'

For Each rngCell In rngAvailable
If rngCell.Value = vbNullString Then
rngCell.Value = ActiveCell.Value
bolSuccess = True
Exit For
End If
Next

If Not bolSuccess Then
MsgBox "Ran outta spaces...", 0, ""
End If


is there also a way to verify if a given name has already been exported?

Does this do it? (untested):

Code:
--------------------

Sub ClickAdd()
Dim rngAvailable As Range, rngCell As Range, bolSuccess As Boolean
If Range("F4") = "y" Then
Set rngAvailable = ThisWorkbook.Worksheets("Sheet1").Range("B18:B40")
For Each cll In Range("A4:C4,I4:Q4").Cells
If Application.WorksheetFunction.CountIf(rngAvailable , cll.Value) = 0 Then
For Each rngCell In rngAvailable
If rngCell.Value = vbNullString Then
rngCell.Value = cll.Value
bolSuccess = True
Exit For
End If
Next
If Not bolSuccess Then
MsgBox "Ran outta spaces... couln't place " & cll.Value & " from " & cll.Address, 0, ""
End If
Next cll
End If
End If
End Sub

--------------------


James;608919 Wrote:
right now this code exports to another sheet, and only exports the
active
cell. I would like to export a given cell range in a row (A4:C4,
F4,I4:Q4),
with a comfirmation based on F4 being "y" vs "n".

Sub ClickAdd()

Dim rngAvailable As Range, rngCell As Range, bolSuccess As Boolean

Set rngAvailable = ThisWorkbook.Worksheets("Sheet1").Range("B18:B40")
'Range Of Cells that needs to change'

For Each rngCell In rngAvailable
If rngCell.Value = vbNullString Then
rngCell.Value = ActiveCell.Value
bolSuccess = True
Exit For
End If
Next

If Not bolSuccess Then
MsgBox "Ran outta spaces...", 0, ""
End If


is there also a way to verify if a given name has already been
exported?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=169024

Microsoft Office Help

I get a "Compile Error"; "Next without For" on

End If
Next cll <----
End If
End If
End Sub

James;610481 Wrote:
I get a "Compile Error"; "Next without For" on

End If
Next cll <----
End If
End If
End Sub

Try:

Code:
--------------------

Sub ClickAddpd()
Dim rngAvailable As Range, rngCell As Range, bolSuccess As Boolean
If Range("F4") = "y" Then
Set rngAvailable = ThisWorkbook.Worksheets("Sheet1").Range("B18:B40")
For Each cll In Range("A4:C4,I4:Q4").Cells
If Application.WorksheetFunction.CountIf(rngAvailable , cll.Value) = 0 Then
For Each rngCell In rngAvailable
If rngCell.Value = vbNullString Then
rngCell.Value = cll.Value
bolSuccess = True
Exit For
End If
Next rngCell
End If
If Not bolSuccess Then
MsgBox "Ran outta spaces... couldn't place " & cll.Value & " from " & cll.Address, 0, ""
End If
Next cll
End If
End Sub

--------------------

Again untested. I'm not sure if it'll try to copy the blanks cells in
Range("A4:C4,I4:Q4")


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=169024

Microsoft Office Help

tested and I no longer get an error, but the data is not exporting either...

Tested, no longer any errors but the data is not transfering. Tried
highlighting all the cells in the row, just F4 and just A4. No joy

James;611491 Wrote:
Tested, no longer any errors but the data is not transfering. Tried
highlighting all the cells in the row, just F4 and just A4. No joy

Did you tweak/correct the name of the sub:
Sub ClickAddpd()
to
Sub ClickAdd()
?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=169024

Microsoft Office Help

Right, tested this time (there were other problems):
This is case sensitive for the 'y' in F4 but case insensitive for the strings being copied.

---
frmsrcurl: http://msgroups.net/microsoft.public...c-cell-s-range

do i need to highlight the entire row? which cell needs to be avtive in order
for this to transfer?

I have tried to have cell A4 active, F4 active, the entire row highlighted,
and F4 in the same case as the code (whioch it was before). I have the "Call
ClickAdd" coded to a button on a page labeled JohnSmith, and i have a sheet
in the same workbook labeled Sheet1. Still not seeing the data transfering.
Am I missing something?

James;612375 Wrote:
do i need to highlight the entire row? which cell needs to be avtive in
order
for this to transfer?

The only thing which needs to be active is the sheet which has the
range A4:C4,I4:Q4 on. It doesn't matter what cells are selected.

It looks as if the code isn't even running. To test this, temporarily
add a line:
Stop
as the first line after 'Sub ClickAdd()'
Then try again.
If it's running, it should stop at that line with a yellow highlight in
the VBE.
You can then step through the code with F8 or let it continue running
unfettered with F5. If you step through the code you can make sure that
the code is following the path you'd expect it to.
Once confirmed that it's at least being called, you should be able to
remove the Stop line again.
If this doesn't happen, then you're not calling the routine at all and
you need to adjust how you're calling it.
In any event, you should be able to call it from the sheet with
Tools|Macros etc.

Of course, if the button is on a sheet called John Smith (or is it the
button labelled John Smith?), and that sheet doesn't have the data on it
in that range A4:C4,I4:Q4, then when you press the button, John Smith is
the active sheet at that point, so nothing is going to be transferred.
Naturally, if you want data to be transferred from or to inactive sheet
that can be tweaked in the code.

Also, what is the name of the code module that you've put this code in?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=169024

Microsoft Office Help

the sheet [John Smith] has a button at cell B35 lableed {exoprt January}
the code behind the buttons is

Private Sub CommandButton1_Click()
Call ClickAdd
End Sub

under module1 sits the code you offered that i have been testing to try to
get to work for what i am doing.

Sub ClickAdd()
Dim rngAvailable As Range, rngCell As Range
If Range("F4") = "y" Then 'case sensitive
Set rngAvailable = ThisWorkbook.Worksheets("Sheet1").Range("A4:A20")
For Each cll In Range("A4:C4,I4:Q4").Cells
If Application.WorksheetFunction.CountIf(rngAvailable , cll.Value) = 0 Then
If Application.WorksheetFunction.CountBlank(rngAvaila ble) 0 Then
rngAvailable.SpecialCells(xlCellTypeBlanks)(1) = cll.Value
Else
MsgBox "Ran outta spaces... couldn't place " & cll.Value & " from "
& cll.Address & vbLf & "Stopping.", 0, ""
Exit Sub
End If
End If
Next cll
End If
End Sub

Fields Cells A4:Q34 have data in them, with Cell F4 as "y", where as A5:Q35
again has data, but F5 is a "n", and where as A6:Q36 is the exactly the same
as A4:Q34...

so when i click the button I do not get the data transfered. I wish there
was a way to post my sheet so i could show you what i mean, as far as what is
occuring.

Well, that seems in order but
temporarily add a line:
Stop
as the first line after 'Sub ClickAdd()'
Then try again.
What happened?
I wish there was a way to post my sheet so i could show you what i mean,
as far as what is occuring.
Thecodecage.com, where I am posting, will allow attachments, but you
have to register (and you may have to post a few messages before it
allows you to attach anything). You could PM me there (Private Message)
if you register. Otherwise if you leave an email address that you don't
mind showing to the world, I'll use it to contact you directly. I won't
be around today, but will look at things tomorrow.
'Thread here.' (http://tinyurl.com/yaamczd)


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=169024

Microsoft Office Help