PDA

View Full Version : Solved: data going to wrong cell



Pete
07-01-2008, 11:15 AM
Hi

Need to find a solution to the macro code below. everytime i run the code the data go to row 1 column F.... i believe the problem lie with the section of code that relates to (i add ws.cells to the macro and i believe this is causing it to populate the final answer to row 1 column F) and not to the table.....Apologies cannot post the worksheet as the file to to large even when zipped.




'Loop through each column in current row
curCol = 6
While (ws.Cells(5, curCol).Value <> "")
'Assign formula
ws.Cells(startrow + 1, curCol).Formula = Replace("=(('Pricing Demand'!^1" & startrow + 1 & "*(1-'Shipping BOG'!^1" & startrow + 1 & "))*'Modelling (Vol)'!^1" & startrow + 1 & ")", "^1", Mid(Cells(1, curCol).Address, 2, InStr(2, Cells(1, curCol).Address, "$") - 2))
curCol = curCol + 1
Wend
End If




ElseIf ws.Name = "Revenue" Then
Set lastSupplierRow = ws.Range("B:B").Find("Supply").Offset(1, 0)
Do While lastSupplierRow.Row < ws.Range("C" & ws.Rows.Count).End(xlUp).Row
Set firstSupplierRow = lastSupplierRow
Do While lastSupplierRow.Value = firstSupplierRow.Value
Set lastSupplierRow = lastSupplierRow.Offset(1, 0)
Loop
foundDemand = False
For rngRow = firstSupplierRow.Row To lastSupplierRow.Offset(-1, 0).Row
'Debug.Print ws.Range("B" & rngRow).Value & " : " & ws.Range("B" & rngRow).Offset(0, 1).Value
If LCase(ws.Range("B" & rngRow).Offset(0, 1).Value) = LCase(TextBox1.Value) Then foundDemand = True
Next
If Not foundDemand Then
lastSupplierRow.Offset(-1, 0).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
' lastSupplierRow.EntireRow.Borders(xlEdgeTop).LineStyle = xlNone
lastSupplierRow.Offset(-2, 0) = firstSupplierRow.Value
lastSupplierRow.Offset(-2, 1) = TextBox1.Value
lastSupplierRow.Offset(-2, 2) = "$MM"
lastSupplierRow.Offset(-2, 3) = "Rev_" & firstSupplierRow.Value & "_" & TextBox1.Value
sup = lastSupplierRow.Offset(-2, 0)
sup = Replace(sup, "+", "_plus")
sup = Replace(sup, ", ", "~")
sup = Replace(sup, " ", "_")
sup = Replace(sup, "~", ", ")
sup = Replace(sup, "-", "_")
sup = Replace(sup, "/", "_")
sup = Replace(sup, "(", "")
sup = Replace(sup, ")", "")
dem = lastSupplierRow.Offset(-2, 1)
dem = Replace(dem, "+", "_plus")
dem = Replace(dem, ", ", "~")
dem = Replace(dem, " ", "_")
dem = Replace(dem, "~", ", ")
dem = Replace(dem, "-", "_")
dem = Replace(dem, "/", "_")
dem = Replace(dem, "(", "")
dem = Replace(dem, ")", "")
'Loop through each column in current row
curCol = 6
While (ws.Cells(5, curCol).Value <> "")
'Assign formula
ws.Cells(startrow + 1, curCol).Formula = Replace("=(('Pricing Demand'!^1" & startrow + 1 & "*(1-'Shipping BOG'!^1" & startrow + 1 & "))*'Modelling (Vol)'!^1" & startrow + 1 & ")", "^1", Mid(Cells(1, curCol).Address, 2, InStr(2, Cells(1, curCol).Address, "$") - 2))
curCol = curCol + 1
Wend
End If
Set lastSupplierRow = lastSupplierRow.Offset(1, 0)
Loop
lastSupplierRow.Offset(0, 1).Borders(xlLeft).LineStyle = xlContinuous
lastSupplierRow.Offset(0, 2).Borders(xlLeft).LineStyle = xlContinuous
lastSupplierRow.Offset(0, 3).Borders(xlLeft).LineStyle = xlContinuous
lastSupplierRow.Offset(0, 4).Borders(xlLeft).LineStyle = xlContinuous
lastSupplierRow.Offset(0, 0).Borders(xlLeft).LineStyle = xlContinuous
lastSupplierRow.Offset(0, 0).Borders(xlLeft).Weight = xlMedium
lastSupplierRow.Offset(0, 131).Borders(xlRight).LineStyle = xlContinuous
lastSupplierRow.Offset(0, 131).Borders(xlRight).Weight = xlMedium
lastSupplierRow.Resize(1, 132).Borders(xlEdgeTop).LineStyle = xlContinuous
lastSupplierRow.Resize(1, 132).Borders(xlEdgeTop).Weight = xlMedium
'Next supplier

mdmackillop
07-01-2008, 11:19 AM
Hi Pete,
Use the VBA button to add tags to post code, ansd also linebreaks to avoid the need to scroll.

Pete
07-01-2008, 11:23 AM
sorry about that.....was not thinking.....

Pete
07-01-2008, 11:34 AM
see book one this is where the data ends up and not in the table with blank spaced row baja

Pete
07-01-2008, 12:36 PM
startrow = lastSupplierRow.Row - 3

'Assign formula
ws.Cells(startrow + 1, curCol).Formula = Replace("=(('Pricing Demand'!^1" & startrow + 1 & "*(1-'Shipping BOG'!^1" & startrow + 1 & "))*'Modelling (Vol)'!^1" & startrow + 1 & ")", "^1", Mid(Cells(1, curCol).Address, 2, InStr(2, Cells(1, curCol).Address, "$") - 2))
curCol = curCol + 1
Wend
End If