|
|
|
|
Junior Member
|
|
Feb 28, 2011, 03:15 AM
|
|
Can VBA do this for me?
Refer attachment;
For sheet "SPPA" cell "D18" value to be recorded on sheet "HO plan" cell "T7", and this is where it gets nasty, as the date rolls over on the "SPPA" sheet cell "F1", so must the macro record the new value in the next cell for sheet "HO plan", ie: If "SPPA" sheet cell "F1" change to "Mon 28 Feb", then the new value must be recorded on "HO plan" cell "U7", and the same for the other colour filled cells.
Thank you.
|
|
|
Software Expert
|
|
Feb 28, 2011, 02:06 PM
|
|
This will do that:
Code:
Option Explicit
Sub RecordDailyUnits()
Dim Col As Long
Dim LINES As Range, Ln As Range, LnFND As Range
Dim wsHO As Worksheet
Set wsHO = Sheets("HO plan")
With Sheets("SPPA")
Set LINES = .Range("A:A").SpecialCells(xlConstants)
Col = wsHO.Cells.Find(Day(.[F1]), LookIn:=xlValues, LookAt:=xlWhole).Column
For Each Ln In LINES
Set LnFND = wsHO.Cells.Find(Ln, LookIn:=xlValues, LookAt:=xlWhole)
Set LnFND = wsHO.Cells.Find("Booked", LnFND, LookIn:=xlValues, LookAt:=xlWhole)
If LnFND Is Nothing Then GoTo ErrorExit
wsHO.Cells(LnFND.Row, Col).Value = Ln.Offset(0, 3).Value
Set LnFND = Nothing
Next Ln
End With
Exit Sub
ErrorExit:
MsgBox "Could not find '" & Ln & "' on sheet HO Plan. Update sheet so there is a match."
End Sub
I also cleaned up some of the other macros in that wb.
|
|
|
Junior Member
|
|
Mar 1, 2011, 02:34 AM
|
|
Very cool, thank you JB!
Where you set
Code:
With Sheets("SPPA")
Set LINES = .Range("A:A").SpecialCells(xlConstants
, do I have data which I deleted to make reading easier, the data in some cells are , and , when running the macro with this data in cells is macro giving me a error.
|
|
|
Junior Member
|
|
Mar 1, 2011, 02:57 AM
|
|
How do one add a new attachment when answering a question? I don't se anything here.
|
|
|
Software Expert
|
|
Mar 1, 2011, 06:48 PM
|
|
Use the GO ADVANCED button below, then the paperclip icon.
|
|
|
Junior Member
|
|
Mar 1, 2011, 11:04 PM
|
|
Comment on JBeaucaire's post
There is no GO ADVANCED button anywhere on this page!
|
|
|
Software Expert
|
|
Mar 2, 2011, 04:50 AM
|
|
Try clicking the QUOTE button, see if that gives you the advanced editor. If not, talk to a forum moderator.
|
|
|
Junior Member
|
|
Mar 2, 2011, 10:37 PM
|
|
Comment on JBeaucaire's post
Nothing form the QUOTE button, will talk to moderator, thank you.
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Can this be done using VBA?
[ 5 Answers ]
Ok - I have a spreadsheet that is exported from an application I use at work. It formats the data in a manner that makes it really difficult to manipulate but I think I've worked out a solution that gets me what I need; only it is very tedious.
The data starts in row 16 of the sheet. There is...
VBA Issue
[ 2 Answers ]
Ok, so here's what I am trying to do. I am trying to use a variable sheet name as a Named Range in my procedure but it is not working.
Here's the part of my code that is failing:
ActiveWorkbook.Names.Add Name:=NameRange1, RefersToR1C1:= _
...
Excel vba
[ 1 Answers ]
I am working on an excel sheet vba, I wanted to un hide the cells with a click on one cell above it without using the command button
VBA code
[ 2 Answers ]
Once the month ends, cell "A1" must tick over to zero, where "A1" is used by me as a counter to show how much has been deliverd to date for the month.
Counter in VBA
[ 19 Answers ]
I have an Excel spreadsheet witch I use for my wage calculations.
Annually I have to submit a report to government showing total amount earned by each worker as well as total income tax deducted. Wages are calculated and paid fortnightly.
I have a counter like this example,” Mycount =...
View more questions
Search
|