Solved: Macro to add value to cell based on previous available cell valu

[raghuisnow]

I have a task where in there is fixed date entered in a cell (say D & E merged and has 1/5/2009 row 2 in the merged cell) and the D & E merged has Weekday (1/5/2009) in row 1. Now whenever I click a button it should check for the last date and add a date to next 2 cells merged (F & G) like 1/6/2009 in row 2 and in row 1 it should be Weekday(1/6/2009) with F & G merged.
Please help me.
Please find attached sample sheet

[lucas]

You should avoid merged cells. they will cause you problems in vba.

[raghuisnow]

My requirement is from 3rd row i need to use 2 columns for single date & day (1/5/2009) & Mon will have 2 columns from 3rd row.That is the reason I need to merge. Without merge I was able to make it with VBA code.Please help

[lucas]

format the cells as "center across selection" and use the code you had operating from cell D2

see attachment.

[mdmackillop]

This uses CentreAcrossSelection, and a change in Row 1 format to avoid the If formula

[raghuisnow]

Lucas : This is my code and it does align horizontally as you said but when I click the button
again for next value it display in single column. Please see the code I used and help me to fix this.

Private Sub cmdAddDay_Click()
Dim ColNumb As Integer
Dim ColVal As String
Dim ColNumbVal As Integer
Dim ColNumbMerge As Integer
Dim MergeColName As String
Dim m As Integer
Dim SourceColName As String
Dim ToColName As String
Range("Weekday").Select
For Each col In Range("Weekday")
ColVal = col.Formula
If ColVal = "" Then
ColNumb = col.Column
ColNumbVal = ColNumb - 1
ColNumbMerge = ColNumb + 1
Do While ColNumb > 0
m = (ColNumb - 1) Mod 26
ToColName = Chr(65 + m) + ToColName
Exit Do
Loop
Do While ColNumbVal > 0
m = (ColNumbVal - 1) Mod 26
SourceColName = Chr(65 + m) + SourceColName
Exit Do
Loop
Do While ColNumbMerge > 0
m = (ColNumbMerge - 1) Mod 26
MergeColName = Chr(65 + m) + MergeColName
Exit Do
Loop
Exit For
End If
Next col
If SourceColName <> "" And ToColName <> "" Then
Range(ToColName & 3 & ":" & MergeColName & 3).Select
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
End With
Range(ToColName & 3).Value = Range(SourceColName & 3) + 1
Range(ToColName & 1 & ":" & MergeColName & 1).Select
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
End With
Select Case Weekday(ThisWorkbook.Worksheets("DailyWorkSheet").Range(ToColName & 3))
Case 1
ThisWorkbook.Worksheets("DailyWorkSheet").Range(ToColName & 1).Value = "Sun"
Case 2
ThisWorkbook.Worksheets("DailyWorkSheet").Range(ToColName & 1).Value = "Mon"
Case 3
ThisWorkbook.Worksheets("DailyWorkSheet").Range(ToColName & 1).Value = "Tue"
Case 4
ThisWorkbook.Worksheets("DailyWorkSheet").Range(ToColName & 1).Value = "Wed"
Case 5
ThisWorkbook.Worksheets("DailyWorkSheet").Range(ToColName & 1).Value = "Thu"
Case 6
ThisWorkbook.Worksheets("DailyWorkSheet").Range(ToColName & 1).Value = "Fri"
Case 7
ThisWorkbook.Worksheets("DailyWorkSheet").Range(ToColName & 1).Value = "Sat"
End Select
End If
'For Each Col In Worksheets("DailyWorkSheet").Columns
' MsgBox Col.Formula



'Next Col
'ThisWorkbook.Worksheets("DailyWorkSheet").Range("C3").Value = ThisWorkbook.Worksheets _
("DailyWorkSheet").Range("B3").Value + 1
'C1 = IF((WEEKDAY(C3))=1,"Sun",IF((WEEKDAY(C3))=2,"Mon",IF((WEEKDAY(C3))=3,"Tue",IF((WEEKDAY _
(C3))=4,"Wed",IF((WEEKDAY(C3))=5,"Thu",IF((WEEKDAY(C3))=6,"Fri",IF((WEEKDAY(C3))=7,"Sat","")))))))
'ThisWorkbook.Worksheets("DailyWorkSheet").Range("C3").Formula = "=B3+1"
'Select Case Weekday(ThisWorkbook.Worksheets("DailyWorkSheet").Range("C3"))
'Case 1
' ThisWorkbook.Worksheets("DailyWorkSheet").Range("C1").Value = "Sun"
'Case 2
'ThisWorkbook.Worksheets("DailyWorkSheet").Range("C1").Value = "Mon"
'Case 3
'ThisWorkbook.Worksheets("DailyWorkSheet").Range("C1").Value = "Tue"
'Case 4
'ThisWorkbook.Worksheets("DailyWorkSheet").Range("C1").Value = "Wed"
' Case 5
'ThisWorkbook.Worksheets("DailyWorkSheet").Range("C1").Value = "Thu"
'Case 6
'ThisWorkbook.Worksheets("DailyWorkSheet").Range("C1").Value = "Fri"
'Case 7
'ThisWorkbook.Worksheets("DailyWorkSheet").Range("C1").Value = "Sat"
'End Select
End Sub

[lucas]

post the workbook. I don't have the named ranges in the first workbook you posted.

Post one that gives the error or creates the problem so we can see what you see.

[raghuisnow]

Thank You msmackillop. It worked as I wanted. But the glitch is did not understood the code . Any help regarding that? Also in row 3 I want text "P" and "A" displayed for each date & day and if it is Sat & Sun then the P and A in row 3 should be grey fill color and black text.

[mdmackillop]

Private Sub CommandButton1_Click()
Dim cel As Range
'Find last cell
Set cel = Cells(1, Columns.Count).End(xlToLeft)
'Select 2x2 area; copy to right
cel.Resize(2, 2).Copy cel.Offset(, 2)
'Change date in copied cells
cel.Offset(1, 2) = cel.Offset(1) + 1
'Add AP and colour
With cel.Offset(2, 2).Resize(, 2)
.Value = Array("P", "A")
If Weekday(cel.Offset(1)) > 5 Then
.Interior.ColorIndex = 15
End If
End With
End Sub

[raghuisnow]

Thank You very much. This is what I was expecting. WOW thank you very much.