CODEWITHSUNDEEP
vbaexcel
anon
anon

Reputation: 5

VBA code to input in cell depending on if a cell in a different sheet has a formula

I am hoping someone here can help me. I am trying to create a macro that looks at a cell in one sheet to see if that cell has a formula or not. If it has a formula it inputs a 1 in the same cell on a different sheet, if not it inputs a 0. This is what I have so far but it is giving me a compile error: Next without for. 

Sub FormulaMap()
Dim r As Integer
Dim c As Integer

For c = 9 To 17
   For r = 11 To 18

If Sheets("Data").Cells(c & r).HasFormula = True Then
  Sheets("Map").Cells(c & r).Value = 1
  Else: Sheets("Map").Cells(c & r).Value = 0

Next r

Next c

End Sub

Any help is appreciated.

Upvotes: 0

Views: 279

Answers (5)

PaulG
PaulG

Reputation: 1189

As an alternative, you could try this method... You can pass in different ranges...

Option Explicit

Public Sub Test()

   Dim ws As Worksheet 'Reference the Map sheet
   Dim rData As Range 'Reference the actual range
   Set ws = Worksheets("Map")
   Set rData = Sheets("Data").Range("I11:Q18") 'Can be any range.

   'Do the call..
   Call EnumFormulas(rData, ws)

End Sub

Public Sub EnumFormulas(ByVal SourceData As Range, ByVal Destination As Worksheet)

   Dim rFoundFormulas As Range, rPtr As Range

   'Fill all data to 0's
   Destination.Range(SourceData.Address).Value = 0

   On Error Resume Next
   Set rFoundFormulas = SourceData.SpecialCells(xlCellTypeFormulas)
   On Error GoTo 0

   If Not rFoundFormulas Is Nothing Then
     For Each rPtr In rFoundFormulas
         Destination.Range(rPtr.Address).Value = 1 'Fill in 1 cell.
     Next
   End If

End Sub

Upvotes: 0

Jordan
Jordan

Reputation: 4514

As mentioned by @Nathan_Sav you need to add End If before calling the next r and c, you also need to use a comma to separate the c and r in the Cells function. I'm also assuming that c is a column reference and r is a row reference? Try this:

Sub FormulaMap()

   Dim r As Long
   Dim c As Long

   For c = 9 To 17
      For r = 11 To 18

         If Sheets("Data").Cells(r, c).HasFormula Then
            Sheets("Map").Cells(r, c).Value = 1
         Else
            Sheets("Map").Cells(r, c).Value = 0
         End If

      Next r
   Next c

End Sub

Also note that the VBA Integer type only ranges from -32,768 to 32,767. This is why it's recommended to use Long (–2,147,483,648 to 2,147,483,647) especially when going through rows.

Upvotes: 1

Nathan_Sav
Nathan_Sav

Reputation: 8531

You have no End If at the end of your If Then Else.

Upvotes: 0

user5612655
user5612655

Reputation: 190

Your code should be

   For c = 9 To 17
   For r = 11 To 18

      If Sheets("Data").Cells(c, r).HasFormula = True Then
        Sheets("Map").Cells(c, r).Value = 1
      Else
        Sheets("Map").Cells(c, r).Value = 0
      End If


    Next r

    Next c

Upvotes: 0

Egan Wolf
Egan Wolf

Reputation: 3573

One thing is what Nathan_Sav said, but second thing is that you have to change your Cells statements so there are two arguments inside. The proper way to call it is

Cells(row, column)

So in your code it would be

Cells(r, c)

(if r means row and c - column)

Upvotes: 0

Related Questions