String value being overwritten

sheri100

New Member
Joined
Mar 29, 2010
Messages
15
I have some code that loop through ranges and concatenates strings together based on dates. When I print it out it works correctly but when I try to assign the strings to cells ith doesn't work correctly.

This works:

Code:
debug.print strNewName
and gives me the correct output
Prime1
Prime1 Prime2
Prime2 prime3

This does not:

Code:
owData.Range("B" & rngTypeCell.Row).Value = strNewName
and gives me something like this (always repeating the previous cell value)

Prime1
Prime 1 Prime1 Prime2
Prime 1 Prime1 Prime2 Prime2 prime3

Any suggestions on how to fix this?

Thanks
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Making some assumptions about your naming conventions (good to see those, BTW), but nothing jumps out at me. I would set a watch on strNewName and step the code using your F8 key.
 

sheri100

New Member
Joined
Mar 29, 2010
Messages
15
Thanks, I didn't see any reason it was happening . I'm clearing the variable. Here is the entire section of code. I'm new to this so it may not be pretty.

Code:
   Set rngLob = Range("E2:E65000")
    Set rngPolicyNames = Range("B2:B65000")
    For Each rngLobCell In rngLob
 
        'prevLob = owData.Range("E" & rngLobCell.Row).Value
        strNewName = ""
 
        For Each rngPolicyNamesCell In rngPolicyNames
            If rngPolicyNamesCell.Row = rngLobCell.Row Then
                'values gets set here
                strNewName = strNewName & " " & owPolicies.Range("B" & rngPolicyNamesCell.Row).Value
                Exit For
            End If
 
            If owData.Range("E" & rngPolicyNamesCell.Row).Value = "" Then
 
                If StrComp(Range("E" & rngLobCell.Row).Value, Range("E" & rngPolicyNamesCell.Row).Value, vbTextCompare) = 0 Then
                    strNewName = strNewName & " " & owPolicies.Range("B" & rngPolicyNamesCell.Row).Value
            End If
 
            ElseIf CDate(owData.Range("E" & rngPolicyNamesCell.Row)) > CDate(owPolicies.Range("C" & rngLobCell.Row)) Then
 
                If StrComp(Range("E" & rngLobCell.Row).Value, Range("E" & rngPolicyNamesCell.Row).Value, vbTextCompare) = 0 Then
 
                    strNewName = strNewName & " " & owPolicies.Range("B" & rngPolicyNamesCell.Row).Value
 
                End If
 
            End If
 
        Next rngPolicyNamesCell
 
    'fix here
       owPolicies.Range("B" & rngLobCell.Row).Value = strNewName
       ' MsgBox strNewName
       'Debug.Print strNewName
 
        If owData.Range("A" & rngPolicyNamesCell.Row).Value = "" Then
              Exit For
        End If
 
    Next rngLobCell
 

sheri100

New Member
Joined
Mar 29, 2010
Messages
15
Figured it out, I was referencing the wrong sheet here:

Code:
If owData.Range("A" & rngPolicyNamesCell.Row).Value = "" Then
              Exit For
        End If
Thanks for the help.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,954
Messages
5,514,368
Members
408,999
Latest member
Virgothinker

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top