=IF((Weeks!D(B1+6))="",1,2)
whats the error with this?
D(B1+6) is ment to respresent a cell with D?
?= the value of B1+6
what am i getting wrong?
If This Helps The Cell Forumla is Going to Be used this way
Sub NamesDropDown_Change()
' check for already inputted data
For x = 1 To 7
Cells(x + 2, "K").Forumla = "=IF((Weeks!D(B1+6))="",1,2)"
Next x
End Sub
But currently this formula is not working
its oki have resolved the problem with his VB code instead of formula more script but works fine.
Sub NamesDropDown_Change()
' check for already inputted data
Dim B1Value As Integer
B1Value = Cells(1, "B").Value
For x = 1 To 7
i = i + 3
If Sheets("Weeks").Cells(B1Value + 6, i).Value = "" Then
Sheets("Add Details").Cells(x + 2, "G").Value = 1
Else
Sheets("Add Details").Cells(x + 2, "G").Value = 2
End If
Next x
End Sub
Not everybody who reads/posts here is working in Excel VBA. If people view and don't reply, they could be just thinking of an answer, or they might simply not be familiar with your problem. Complaining about not getting an answer won't help bro 
Hello Milkshake,
This is only a suggestion, but I prefer to stay away from "hard coding" values in my statements. eg
If Sheets("Weeks").Cells(B1Value + 6, i).Value = "" Then
Sheets("Add Details").Cells(x + 2, "G").Value = 1
Else
Sheets("Add Details").Cells(x + 2, "G").Value = 2
End If
Next x
It is better to use declare and use variables instead, this leaves you much more scope for later and is much easier to incorporate code in loops if required. If you remember by adopting this practise, your "mammoth" code in your previous post was reduced from a few pages to just 20 or so lines of code.
Like I said before, it's only a suggestion.
Chas
hi not quite sure that i understand what u mean, would you be able to provide al little code so i can understand your train of thought?
I left this section "G" as i am only working with that column for this particlular code
Sheets("Add Details").Cells(x + 2, "G").Value = 2
what i wanted to do was give cells 3-9 in column G a value of either 1 or 2 after checking the value of another cell in another worksheet, once my cell done this i used conditional formatting to change the colour of that cell
i did this so if the colour was green (value of 1) i know that the cell which the code checked had no data and was free to add more data, if the cell was gray (value of 2) then i know that ther already was data in the cell, i can then go on to do a msgBox to ask if i would like to overwrite the data in that cell or not.
which i have also already coded works quite well :)
i got it all to work and i am very proud :P only been learning for like 2 days and nights
Also in regards to getting annoyed i appologise, it was 2am and i was staring at a code that i Couldnt Fix :P appologises all round, everyone really does help.
Hello Milkshake,
What I was trying to say was, suppose you had these two bits of code:
Code version 1
f Sheets("Weeks").Cells(B1Value + 6, i).Value = "" Then
Sheets("Add Details").Cells(x + 2, "G").Value = 1
Else
Sheets("Add Details").Cells(x + 2, "G").Value = 2
End If
Next x
Code version 2
colOffset=7 ' G being the seventh character of the alphabet ie column G
If Sheets("Weeks").Cells(B1Value + 6, i).Value = "" Then
Sheets("Add Details").Cells(x + 2, colOffset).Value = 1
Else
Sheets("Add Details").Cells(x + 2, colOffset).Value = 2
End If
Next x
Lets say your column reference changed for whatever reason to Column H
in Code version 1 you would have change all the lines (I know only two in this case, but could be many) to read Cells(x+2,"H")
whereas in version 2 you would just have to change the line:
colOffset=8
It's just something that I have found to be helpful in the past, that's all, nothing wrong in your way either.
Chas
