0

Using Excel 2007 and inexperienced with VBA.

I am trying to create a text generator where, given a particular type of entry, it returns a string of text AND a number of corresponding cells.

I have a lot of data to combine and somewhat complex text statements. The data I have here is sample data. The final file will combine multiple text strings ("one" and "two", etc) with multiple cells that need to be referenced based on the specific data input there (some instances require 8 different cells).

I was able to figure out how to do Select Case where it can look up the type of entry I need, and it returns a specific text string. Great! I also know I can combine things like "One" & "Two". But how do I combine the corresponding text for the line?

Where if the entry was "ID" I could return something that looked like "One 1" but combines "One " & K2 as the .Value to return?

Image shows the sample data

enter image description here

Image shows data for Columns I, J, and K. Column I stores the entry type, J is where the data is returned, and K is the column with data to be referenced. Again, what's here is the simplified reference data.

I know this line is wrong. But I'm not sure what to put in place for K2 so it will correctly reference the cell, and then loop correctly for 300 times. 

Case Is = "ID": .Value = "One" & (K2)

Am I close to something workable? What would you suggest? Is Select Case the wrong way to handle this problem?

I've tried a number of options, but I am inexperienced with VBA. I think whatever I implemented to fix the issue was likely done incorrectly.

Full code of what I have shown below. 

Private Sub CommandButton1_Click()

Dim logtype As Range

For Each logtype In Range("I2:I302")
    With logtype.Offset(0, 1)

        Select Case logtype.Value
            Case Is = "ID": .Value = "One" & K2
            Case Is = "Phase": .Value = "Two"
            Case Is = "Install New": .Value = "Three"
            Case Is = "Install OH": .Value = "Four"
           Case Is = "Install AR": .Value = "Five"
            Case Is = "Insp": .Value = "Six"
            Case Is = "LUI": .Value = "Seven"
            Case Is = "": .Value = ""
            Case Else: .Value = "Not Recognized"
        End Select
    End With
Next logtype

End Sub

EDIT:

What I put in a comment below, just formatted more clearly.

I want to create a macro that spits out the correct language of an entry based on the kind of work completed in a work order.

For example, ID or Install New and then return the correct language and reference data for the entry:

I2: ID

K2: Number (I'd type in the actual No.)

Returns:

"Complied with ID No. [K2]. No defects noted."

I4: Install New

L4: Actual part number

M4: etc...

N4: ...

O4: ...

Returns:

"Removed P/N [L4], S/N [M4]. Installed P/N [N4], S/N [O4]. Ops checked good."

Does that make sense? Thanks!

EDIT 2:

Hey everyone, I've tried tracking down alternative methods of solving this problem but I still haven't produced anything better than what I have here.

Any thoughts or help would be greatly appreciated. Thank you!

Stephanie Siegel
  • 1
  • 1
  • 1
    beyond `NA` in the table you've shown, what will the data look like? I am assuming that the I and J columns will have the same info, but the value in K may be something other than 1 thru 8 – SmileyFtW Dec 11 '19 at 23:11
  • Also if you could mockup an example of what the output should be would likely be helpful. – Mike Dec 11 '19 at 23:14
  • it looks like you are replacing the value in J with its original value appended with the value in K. Is that what you're after? If so, the solution below will do that. – SmileyFtW Dec 11 '19 at 23:17
  • The solution below isn't what I'm looking for. I want to create a macro that spits out the correct language of an entry based on the kind of work completed in a work order. For example, ID or Install New and then return the correct language and reference data for the entry: I2: ID K2: Number (I'd type in the actual No.) Returns: "Complied with ID No. [K2]. No defects noted." I4: Install New L4: Actual part number M4: etc... N4: ... O4: ... "Removed P/N [L4], S/N [M4]. Installed P/N [N4], S/N [O4]. Ops checked good." Does that make sense? Thanks! – Stephanie Siegel Dec 11 '19 at 23:57

1 Answers1

0
For each cell in Range("I2:I302")
   cell.offset(0,1).value=cell.offset(0,1).text & cell.offset(0,2).text
next
SmileyFtW
  • 326
  • 2
  • 10