1

We get daily summary emails into an inbox for all of our clients, but they come from the same email address so when we export them, the customers are all listed as the same email. The subject includes the customer name, so I need to copy ONLY the customer name from a cell in column 'M' in Excel, into a cell in column 'B'. This needs to happen for every single row in the worksheet, but I'm a little stuck on the code side. Please could I have some assistance?

For reference, the email subject we receive is: NEW ALERT (customer name > alert type > device)

So I'd want to be picking out the customer name, which would be between '(' and '>'. I'd then want to paste this in a cell in the same row, in column 'B'.

I'd want this to repeat for every cell in column 'M'.

I've tried various snippets of code, but none of them are the full deal, and my experience is so lackluster in VB I'm not entirely sure what I'm doing.

So my current code is as follows.

Sub CustomerNames()

Dim str As String
Dim openPos As Integer
Dim closePos As Integer
Dim midBit As String

str = ActiveSheet.Range("M" & Rows.Count).Select
openPos = InStr(str, "(")
closePos = InStr(str, ">")
midBit = Mid(str, openPos + 1, closePos - openPos - 1)


End Sub

So the results I'd like would be that the Macro would check every cell in column 'M', looking between the '(' and '>' character in the string to find the customer name. It should then copy the string it's found to the corresponding cell in the same row in column 'B'.

The actual output makes no change to the spreadsheet at the moment.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
PureIT
  • 11
  • 1
  • To begin with, you need to loop through the cells in the range. See [this](https://stackoverflow.com/questions/3875415/loop-through-each-cell-in-a-range-of-cells-when-given-a-range-object). – cybernetic.nomad Oct 04 '19 at 14:41

1 Answers1

0

You need to use a loop, there are loads of tutorials available, it's really easy and I suggest you look into it :)

Here's my approach, assuming your data is in M2:M10, workbook is ThisWorkbook, and sheet name is test - change accordingly. Code will output your midBit in column B, and row number in column A.

'set workbook and worksheet objects
Set wb = ThisWorkbook
Set ws = wb.Sheets("test")

'set range
Set rng = ws.Range("M2:M10")

'loop through each cell in set range
For Each cel In rng
    With cel
        openPos = InStr(.Value, "(") + 1
        closePos = InStr(.Value, ">") - 1
        midBit = Mid(.Value, openPos, closePos - openPos)
        'update value(s)
        ws.Cells(.row, "B").Value = midBit
        ws.Cells(.row, "A").Value = .row
    End With
Next

Bear in mind this is very basic code and there are no declarations or error handling, this is something you're gonna have to do yourself. It's just to give you something to start with.

Daniel
  • 814
  • 6
  • 12
  • So if I was to use that, how would I then replace the value in the 'A' cell in the same row with the value I've obtained from that? – PureIT Oct 07 '19 at 11:11
  • `.Offset(0, -12).Value = "your_value"` would be cell A, 12 to the left from `cel` which is column M. I've done it this way only because you didn't set workbook/worksheet object(s), instead of offset you can just use `ThisWorkbook.Sheets("test").Cells(.row, "A").Value = "your_value"` which is easier to read and does the same thing. – Daniel Oct 09 '19 at 09:30
  • I've edited the code so that wb and ws objects are set and it's easier to read/modify for you. – Daniel Oct 09 '19 at 09:38