0

I am attempting to write code to enter a formula into a particular cell prior to hiding the entire column however I am having trouble with the syntax of the formula itself.

So far I have defined the variable i as a string and it is defined correctly. i will have a text value of jan, feb, mar, etc.

Below is what code I have currently:

Dim i as string

For Each cell In range("B4:M4")
    If cell.Value = "No Data" Then
        cell.Select
        i = Selection.Offset(-1, 0).Value
        Selection.Formula = "=+" & i & "18'!E66"
        Selection.EntireColumn.Hidden = True
    End If
Next cell

Any help would be greatly appreciated! Thanks!

Taazar
  • 1,545
  • 18
  • 27
Derek Harden
  • 41
  • 5
  • 3
    What should be the result of your formula? How would the formula look like in the cell? Please give proper example data. • Probably the `+` sign in your formula should be a `'`? • Also I recommend not to use `.Select` and `Selection.` instead use directly `cell.` See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Oct 09 '18 at 13:39
  • A few notes: 1) Since you have a variable `cell`, you can use that instead of "Selection"; i.e. `cell.Offset(-1, 0).Value` instead of `Selection.Offset(-1, 0).Value` or `cell.Formula =....` instead of `Selection.Formula =...` 2) there is a pre-defined `Cells` analogous to `Range` (uses row,column integers instead of letter-number), so it would be less confusing if you used a variable name other than `cell`. – Mistella Oct 09 '18 at 13:45
  • 2
    3) It is very common that single letter variables like `i` are used for counter variables only. So most developers will assume that this is a counter. Using a more descriptive variable name like `MonthName` or something similar would be a good practice. – Pᴇʜ Oct 09 '18 at 13:49
  • what happens if you just delete the `+`? are you trying to link cells or adding the value in `i & "18'!E66"` to active cell? Because it is not the same – Foxfire And Burns And Burns Oct 09 '18 at 13:56
  • 1
    @PEH @ Mistella thanks for the advice! Still fairly new to the VBA. The use of cell rather than selection helped me out and I'm where I need to be! That and placing a space where one was lacking! Thanks again for the assistance! – Derek Harden Oct 09 '18 at 14:05

0 Answers0