Simple Copy to and From Cells With IF statement.

[omen666blue]

Hi all Me again
im working on a diet form for you GF and i want to make it nice an
simple to use basically i have got a macro at the minute that when yo
click on a button it copys the calories eaten on mon,tue,wed ect.
then goes to a "Weigh in" Sheet and Pastes in a Specific cells
What i Now need it to do (because the first week is now complete) is t
Paste To the next week

So If N8 = (Value) Then Goto Cell N9 If that cell Has data in it the
goto N10 ect
When it finds a N Cell without Data Paste the Dim Mon(Dim Mon Is se
From the origional sheet Befor If Statements)

Then Rince and Repeat for Tue in O8 ect ect

here is My current Macr
---------------------------------------------------------------------------------
Sub Transfer_Sara(

' Transfer_Sara Macr
' Transfers Saras Eaten List to Weigh in shee



Dim Mon As Intege
Dim Tue As Intege
Dim Wed As Intege
Dim Thu As Intege
Dim Fri As Intege
Dim Sat As Intege
Dim Sun As Intege

' Makes The New integers the same as on the Meal calc shee
' Mon = Worksheets("Sheet 1").Range("A65536").End(xlUp).Ro
Mon = Worksheets("Meal Planner").Range("C22"
Tue = Worksheets("Meal Planner").Range("C23"
Wed = Worksheets("Meal Planner").Range("C24"
Thu = Worksheets("Meal Planner").Range("C25"
Fri = Worksheets("Meal Planner").Range("C26"
Sat = Worksheets("Meal Planner").Range("C27"
Sun = Worksheets("Meal Planner").Range("C28"

'Transfer dat

Sheets("Weigh in").Range("N8") = Mo
Sheets("Weigh in").Range("O8") = Tu
Sheets("Weigh in").Range("P8") = We
Sheets("Weigh in").Range("Q8") = Th
Sheets("Weigh in").Range("R8") = Fr
Sheets("Weigh in").Range("S8") = Sa
Sheets("Weigh in").Range("T8") = Su

End Su
-----------------------------------------------------------------
How would i go about adding the IF statement to this

once i Know for one Colum i can figure out the rest :
Just Learning as i go at the minute!
Thanks for the Help in advance

Chri

 

[Spencer101]

Hi all Me again!
im working on a diet form for you GF and i want to make it nice an
simple to use basically i have got a macro at the minute that when yo
click on a button it copys the calories eaten on mon,tue,wed ect.
then goes to a "Weigh in" Sheet and Pastes in a Specific cells.
What i Now need it to do (because the first week is now complete) is t
Paste To the next week.

So If N8 = (Value) Then Goto Cell N9 If that cell Has data in it the
goto N10 ect.
When it finds a N Cell without Data Paste the Dim Mon(Dim Mon Is se
From the origional sheet Befor If Statements).

Then Rince and Repeat for Tue in O8 ect ect.

here is My current Macro
----------------------------------------------------------------------------------
Sub Transfer_Sara()
'
' Transfer_Sara Macro
' Transfers Saras Eaten List to Weigh in sheet
'

'
Dim Mon As Integer
Dim Tue As Integer
Dim Wed As Integer
Dim Thu As Integer
Dim Fri As Integer
Dim Sat As Integer
Dim Sun As Integer

' Makes The New integers the same as on the Meal calc sheet
' Mon = Worksheets("Sheet 1").Range("A65536").End(xlUp).Row
Mon = Worksheets("Meal Planner").Range("C22")
Tue = Worksheets("Meal Planner").Range("C23")
Wed = Worksheets("Meal Planner").Range("C24")
Thu = Worksheets("Meal Planner").Range("C25")
Fri = Worksheets("Meal Planner").Range("C26")
Sat = Worksheets("Meal Planner").Range("C27")
Sun = Worksheets("Meal Planner").Range("C28")

'Transfer data

Sheets("Weigh in").Range("N8") = Mon
Sheets("Weigh in").Range("O8") = Tue
Sheets("Weigh in").Range("P8") = Wed
Sheets("Weigh in").Range("Q8") = Thu
Sheets("Weigh in").Range("R8") = Fri
Sheets("Weigh in").Range("S8") = Sat
Sheets("Weigh in").Range("T8") = Sun

End Sub
------------------------------------------------------------------
How would i go about adding the IF statement to this?

once i Know for one Colum i can figure out the rest
Just Learning as i go at the minute!
Thanks for the Help in advance!


Chris

Have a look at how to use xlToRight and offset. This will allow you t
do what you're trying to do

 

[omen666blue]

Have a look at how to use xlToRight and offset. This will allow you t
do what you're trying to do.

Thanks Spencer, However

I require it go down then right. What would i need to look up for this?
also a problem i have found when finding blank cells ect. is that alo
of the tutorials out there start with A:A were as my Sheet has lots o
tables table headers in Cols N-T Rows 6-7 and Blank Cells in N-T Row
1-5 therefor alot of the tutorials i find dont work Because they ar
based on an empty sheet of a table Based in Cols A-C (for example).

Any chance of some sample code with explanation? Sorry im just fairl
new only been using VBA for like 20 Hours

Thanks For taking the time to reply to this and for your patients

Chri

 

[omen666blue]

Hi Chris,

I've been using VBA a little longer than that, but not by much!

Any chance of a sample workbook?

If you don't want to post it here, feel free to email it to me on pubnu
@ gmail . com (without the spaces obviously).

S.

Added Below

So I need the Buttons to Paste in Weigh in sheet from Meal Calculator :
free to use it as well if ya go on a diet

Have a looksy any Questions dont hessitate to ask


cheers for looking
chri

+-------------------------------------------------------------------
|Filename: Sara's Special Sheet.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=520
+-------------------------------------------------------------------