what is wrong with this formula? *9 Views & no one knows?*

what is wrong with this formula? *9 Views & no one knows?*

• Boris Mueller

  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
  

  
  

• voila jacques

  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

  
  
  

• Danny Tuppeny

  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

  
  
  

• Jamie Thomson

  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

  
  

• jacquelineuy

  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

  
  
  

• AlexBB

  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.

  
  
  
• what is wrong with this formula? *9 Views & no one knows?*