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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,689
Messages
5,833,134
Members
430,195
Latest member
AnalystStoneSteps

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top