Posts by Amapola188

    Hi Jolivanes

    It works. It was still playing up depending on where my cursor (ActiveCell) was but I've included a line to send it to the top so it's working perfectly now.

    So the portion of the code now looks like this:

    Dim myRange As Range
    Dim myEmail As String
    sh2.Cells(1, c).Select
    For Each myRange In sh2.Range(ActiveCell, ActiveCell.Offset(20, 0))
            If IsEmpty(myRange) = True Then
              myEmail = myEmail & sh2.Cells(myRange.Row, 2).Value & ", " & sh2.Cells(myRange.Row, 1).Value & ";"
            End If
    Next myRange

    I had tried


    to send it to the top but for some reason that wouldn't work.

    Thanks for your help, as usual! I am learning. (I'm also having fun.)


    Hi jolivanes

    I'm including the entire code but I just figured out the problem but not sure what to do about it. - The spreadsheets returns that come in and I have to send a reminder to everyone who hasn't sent their badge back by a certain date. So for each month, I move to a different column which is why I need to keep myRange variable. - MyEmail is a container that collects who to send the reminder to.

    r is the row of the ActiveCell but the ActiveCell of course won't move which is why I get the same result over. - So I need to activate the row the For Each statement is looking at but I'm not quite sure how that is done.

    I'm trying to modify a bit of code I have to cycle through a column and if the cell is empty, pick a value along the row. - The modification is to allow for the fact that in this case it's not a fixed column but driven by the ActiveCell.

    I have two issues. I have been playing around with defining the range. What I have now doesn't throw up an error but it doesn't seem to work either because the result is the same many times over.

    • Issue 1 seems to be that the For Each function doesn't cycle through the range.
    • Issue 2 is that the value it gives me is one it shouldn't, i.e. the cell isn't empty.

    It's a very short bit of code and I would have thought fairly simply - so two issues is quite impressive!


    Dim myRange As Range
    Dim myEmail As String
    For Each myRange In sh2.Range(ActiveCell, Cells(ActiveCell.Row + 20, ActiveCell.Column)) 'sh2.Range(ActiveCell, ActiveCell.Offset(20, 0))
            If IsEmpty(myRange) = True Then
               myEmail = myEmail & sh2.Cells(r, 2).Value & ", " & sh2.Cells(r, 1).Value & ";"
            End If
    Next myRange

    This is my original code. The columns are fixed so I work with offset to find the email address.

    Dim myRange As Range
    Dim myEmail As String
    lastrow = sh3.Cells(Rows.Count, 1).End(xlUp).Row 'counts the number of rows in use
    For Each myRange In sh3.Range("A1:A" & lastrow)
    If myRange = "x" Then
    myEmail = myEmail & myRange.Offset(0, 3).Value & ";"
    End If
    Next myRange

    It's working now. The error was that the OR statement needs the object repeated. - I haven't solved the issue of successfully nesting a loop but this is much cleaner anyway.

    For Each rngCommercial In sh3.Range("C1:C" & lastrow)
            If (rngCommercial = "Commercial" Or [COLOR=#FF0000]rngCommercial[/COLOR] = "SalesOps") And rngCommercial.Offset(0, -2).Value = "x" Then
               strCommercial = strCommercial & rngCommercial.Offset(0, -1).Value & " "
            End If
    Next rngCommercial

    I've changed my code around, thinking I might not need to nest a second For loop. Now I'm using Offset to get to the first column to test it and write the If statement with AND and OR to get all the conditions in. But I still get the type mismatch error on the If line.

    All my variables are declared, rngCommercial is a Range, strCommercial is a String and last row Long (the result should be 80). For the life of me, I can't see where there could be a mismatch.

    Dim lastrow As Long
        lastrow = sh3.Cells(Rows.Count, 1).End(xlUp).Row
    Dim rngCommercial As Range 'Column C - function needs to match
    For Each rngCommercial In sh3.Range("C1:C" & lastrow)
           [COLOR=#FF0000] If (rngCommercial = "Commercial" Or "SalesOps") And rngCommercial.Offset(0, -2) = "x" Then[/COLOR]
               strCommercial = strCommercial & rngCommercial.Offset(0, -1).Value & "; "
            End If
    Next rngCommercial

    It sounds like vba is as confused as I am. Because the error message was about a For without a Next. It didn't complain about an open IF statement. - And now the error message says Type mismatch. It also says the issue is with Word. I'm posting the complete code here. I'm gathering all sort of information in the Excel spreadsheet and the plug into a Word Document. At the above stage (and that's where the error is), I'm not talking to Word yet.

    Hi Johnathan

    Thanks for that. I exit the For statement. My reasoning is that if I have checked that is has the x, I'm done. I don't need it to loop further through column A?

    But if I change the code to below, i.e. inserting a Next, I get an error message Next without For. I would understand that as that I have to close one loop before starting another? But I need to check the second condition before it can move on in the first loop. Does that make sense?

    Good Morning - I know my brain would end up in knots trying to solve that one. Nested For loops is scary enough but for added piquancy I need to check out two conditions.

    I have a long list of people. Their name is in Column B. In Column C is their function. I have a formula in Column A that puts an x into the cell if the region matches another criteria. Now I'm trying to add all people that have an x in column A and match the function in column C.

    If I'm trying to step my brain through this, it should work but I get an error "Invalid Next control variable reference".

    Any suggestions very much appreciated. Thanks.

    Hi XenoCode

    All solved! You put me on the right track with the non-existent property. I had FormFields with a named bookmark so it didn't like the bookmarks property, I had to use the FormFields property.

    I also managed to insert all published docs in the folder by writing it directly in that piece of code.

    Next, I'll update the footer as well and I'll be going home at lunch time from now on :)

    Below the code as it's now, all pretty and working. Thanks for your help.

    I can do a mailmerge from Excel via VBA so this was meant to be easy. I have an Excel sheet with data which I want to transfer to a Word document. But nothing I have tried so far has worked and I have scoured the internet high and low for code. - The code opens the Word document but from then on nothing happens. Error Message says Object doesn't support this property or method. Which I think refers to the With block. I have tried other methods but nothing works!

    Any other suggestions would be appreciated. Thanks.

    (Other things the macro is meant to do are read list of files in directory and add in, some variable content and then save in specified folder with specified name.)

    Hi chavezm9, that looks fantastic. I've noticed that further down it brings in lines starting with 423S, User and Report ID. Working on a big tender today so will cross-check next week and if I get bored, I might still try the IsNumeric method. Until then, if it ain't broke ...!!

    Thank you so much and have a great weekend.


    Hi chavezm3. Thanks for this. Quite a different logic, I think I had started it the wrong way. The report headers are not needed.

    I just tried it and can confirm that there are blocks with only one line and it stops at that block and gives an error at this line:

    Ws_1.Range(Ws_1.Cells(Cell.Offset(12, 0), 3), Ws_1.Cells(Cell.Offset(12, 0), 32)).Copy Ws_2.Cells(NextRwTable, 5)

    The lines that need to come across always have only numbers in column A. So rather than going by empty or not empty at offset, can we go by that?

    Thanks, Christine

    Yes, the other data would remain. The end result would be a table with Product Number, Plant, Profit Centre, Storage Location, Description, etc. - all the headers would be removed (my colleague is happy to just sort the data and delete those line once the info in the header has been transferred).
    I have attached an example of what the end result would look like.
    Thanks, Christine

    Good Afternoon,

    I'm trying to write a macro that on the surface seems pretty straight forward but the solution eludes me. I'm trying to turn a report into a table. The sheet will contain blocks of data with some information in the header which I'm trying to add to the individual rows so we can work with the data as a table.

    The report would look something like this (but messier, with heaps of blank rows in between, and repeated blocks of this data):
    Office: XYZ
    Cost Centre: 77777

    Product Number - Blank column - Description
    123456 - Blank column - 123455 something
    0987766 - blank column - 0987766 another thing

    What I'm trying is do is to place the office and cost centre information in between Product Number and Description. I'm utilising the Blank column and adding another one. (that bit works ...). I also have variables for the office and cost centre but I can't manage to write the code that finds the rows where to insert the data.

    Ultimately, I need two loops because it's blocks of data but before I run, I just want to be able to walk to get the code to manage the first block of data. This means I need a loop that inserts Office and Cost Centre in columns B and C in each row that starts with a cell that contains a number only. I'm trying to use ISNUMERIC but nothing I've tried to for has inserted any data anywhere.

    • I need the code to find the point where to start, i.e. the first row with only numbers in column A.
    • I also need the code to stop whent the numbers end. The following row would either be a blank row or start the next block.

    This is the code so far:

    I haven't found a lot of information on ISNUMERIC and at this point, it's likely that both the code for the loop and the IF function has issues.

    Any help will be much appreciated.


    VBA does not like me! The above didn't really get me anywhere so I have now modified my code to only combine sheets from the 3rd sheet onwards. So I have all my admin sheets first and the data sheets 3 to x.

    For this report, I have one "admin" sheet (the pivot table). I add the new data (usually a sheet), then delete the "combined" sheet and run the macro. Seems to work so far! - For the other report, I will change the 3 to 4 and see what happens!

    This is what my code looks like now: