If statement in code to populate form

Question

I have two sheets in a workbook. One contains a form, and the other contains data to populate the form with. I was able to write a small macro which successfully loops and populates the cell, Job Title, in Range("B16:I16") with the information in Column A from Data.

I'm trying to write an If statement within VBA that would populate the cell, Employee Work Location (Building), in Range("D14:H14") depending on the text in Job Title.

I wrote something out, but it doesn't seem to work. I get a mismatch error. May I ask for help with this please?

Thank you!

Sub FormPopulate_Click()
    Dim i As Integer
    Dim Building_Location as String
    For i = 2 To 3
        Sheets("Data").Select
        Range("A" & CStr(i)).Select
        ThisFile = Range("A" & CStr(i))
        Selection.Copy
        Sheets("Form").Select
        Range("B16:I16").Select
        ActiveSheet.Paste
        Sheets("Form").Select
        If Worksheets("Form").Range("B16:I16") = "Coordinator" Then
            Building_Location = "East Quad"
        Else
            Building_Location =""
        End If
        Sheets("Form").Range("D14:H14").Value = Building_Location
     Next i
   End Sub

I removed the irrelevant (commented out) code and indented properly to make it easier for others to read. I don't understand what this is `Title Cell [Column A]`. Please see "[ask]" as well as **how to create a [mcve]** (including removing irrelevant parts of your images) and these [**tips**](https://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) from the sites' top user. Basically, the goal is to make it as **easy as possible** for others to help you. — ashleedawg, Aug 28 '18 at 16:09
@Davesexcel, @ChunkyFresh I _suspect_ it was a wrap-around continuation of the phrase `Job Title Cell` that went with the diagram (edited out) where `[Column A]` is a description of where that cell was. If so, it's not actually part of the code. — Mistella, Aug 28 '18 at 16:36
It seems the if statement I created was left out when I copied and pasted the syntax. Ah, this really bummed me out. Should I report the question? — ChunkyFresh, Aug 28 '18 at 16:54
What are you trying to do? There is no part of the data sheet where a location is linked to any job title, so where do you want to copy from? Why do you loop through the data instead of the form? What is the logic behind your task? — ComradeMicha, Aug 28 '18 at 16:58
I ***highly*** recommend [avoiding the use of `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — BruceWayne, Aug 28 '18 at 17:13

score 0 · Answer 1 · answered Aug 28 '18 at 17:13

This part of the code:
Selection.Copy Sheets("Form").Select Range("B16:I16").Select ActiveSheet.Paste

refers to cell data already copied, but here is being pasted to a range?? This is not (really) a range as it is a merged cell, so the reference should be B16 or B16:B16

Similarly later,
If Worksheets("Form").Range("B16:I16") = "Coordinator" Then ... should also only reference the single cell, as above.

Good luck.

score 0 · Accepted Answer · answered Aug 28 '18 at 17:19

0

The main issue is you can't check multiple cells' values at once. You're trying this with ...Range("B16:I16") = "Coordinator". Instead, use something like COUNTIF().

Sub FormPopulate_Click()
Dim i       As Long
Dim Building_Location As String
Dim dataWS As Worksheet, formWS As Worksheet
Dim thisFile As Range, destRange As Range

Set dataWS = Sheets("Data")
Set formWS = Sheets("Form")
For i = 2 To 3
    Set thisFile = dataWS.Range("A" & i)
    Set destRange = formWS.Range("B16:I16")
    thisFile.Copy destRange

    If WorksheetFunction.CountIf(formWS.Range("B16:I16"), "Coordinator") = destRange.Cells.Count Then
        Building_Location = "East Quad"
    Else
        Building_Location = ""
    End If
    formWS.Range("D14:H14").Value = Building_Location
Next i
End Sub

Another note, you're going to overwrite the data in formWS.Range("B16:I16")in each iteration. Is that okay?

answered Aug 28 '18 at 17:19

BruceWayne

22,923
15
65
110

This answer goes above and beyond! Thank you. If I can post through this comment a follow up. I'm trying to populate this form with information from other columns in Data. I just used one column in this example, because the other columns don't have if statements attached to them. However, essentially what I'm trying to do is have something like this run through multiple columns in Data, populate the form, and export to a PDF. Having a hard time implementing your structure to what I already had. Can I make another post about this? – ChunkyFresh Aug 28 '18 at 17:58
@ChunkyFresh - Yes, of course! In fact, since it's a separate question altogether, a new question is exactly what you should do. (Instead of adding to this one. At SO we like to keep each question focused on one thing, so if this is solved and you have even a related issue, feel free to ask a new question!) – BruceWayne Aug 28 '18 at 18:06

If statement in code to populate form

2 Answers2