Very interesting VBA Coding issue...

[SBAlexanian]

Let me preempt this question with the fact that I am a complete newbie to computer programming, and VBA. I have dabbled in C++ but that really doesn't carry over as much as I had hoped. For some reason, my current employers think a graduate degree in mechanical engineering translates to software engineering Guru.

I have been asked to create an excel program that tracks and updates our current inventory.

What I am trying to do:

There is a sheet with a part code and a rebate value associated with it. There is a separate sheet with a list of our entire inventory organized by these part codes. Our sales team goes in and modifies rebate values in the costing sheets and wants it to update into our inventory sheet.

Here is what I have to this point...

[COLOR=#000000][FONT=arial]Sub GMC_Rebates()[/FONT][/COLOR]

[COLOR=#000000][FONT=arial]Application.ScreenUpdating = False[/FONT][/COLOR]

[COLOR=#000000][FONT=arial]Sheets("GMC Rebates").Visible = xlSheetVisible[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]Sheets("GMC").Visible = xlSheetVisible[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]Sheets("GMC").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]ActiveSheet.Range("D2").Select[/FONT][/COLOR]

[COLOR=#000000][FONT=arial]Dim j As Integer[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]j = 2[/FONT][/COLOR]

[COLOR=#000000][FONT=arial]Do While Cells(j, 4).Value <> ""[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]desc = ActiveCell.Value[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]Sheets("GMC Rebates").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]ActiveSheet.Range("A:A").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]Selection.Find(what:=desc, After:=ActiveCell, LookIn _[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]       :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]        xlNext, MatchCase:=False, SearchFormat:=False).Activate[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]ActiveCell.Offset(0, 1).Select[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]Application.CutCopyMode = False[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]Selection.Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]Sheets("GMC").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]ActiveCell.Offset(0, 14).Select[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]ActiveCell.PasteSpecial xlPasteValues[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]ActiveCell.Offset(0, -14).Select[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]j = j + 1[/FONT][/COLOR]
[COLOR=#000000][FONT=arial]Loop[/FONT][/COLOR]

[COLOR=#000000][FONT=arial]End Sub[/FONT][/COLOR]

Any Help would be greatly appreciated...

 

[Norie]

You mention part code and part description in your posts, which is it that should be used to find the data on 'GMC Rebates'?

 

[SBAlexanian]

Thanks everyone for the replies.... I solved the issue on my own. The issue was that I didn't have the loop reselect the active cell I needed copied...

Sub GMC_Rebates()

Application.ScreenUpdating = False

Sheets("GMC Rebates").Visible = xlSheetVisible
Sheets("GMC").Visible = xlSheetVisible
Sheets("GMC").Select
ActiveSheet.Range("D2").Select

Dim j As Integer
Dim i As Integer
j = 2
i = 4

Do While Cells(j, i).Value <> ""
Cells(j, i).Select [U][I][B]'This was the missing line[/B][/I][/U]
desc = ActiveCell.Value
Sheets("GMC Rebates").Select
ActiveSheet.Range("A:A").Select
Selection.Find(what:=desc, After:=ActiveCell, LookIn _
       :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("GMC").Select
ActiveCell.Offset(0, 14).Select
ActiveCell.PasteSpecial xlPasteValues
ActiveCell.Offset(0, -14).Select
j = j + 1
Loop

End Sub

 

[JackDanIce]

My code doesn't need to loop. It enters a formula of VLOOKUP into J2 of Sheet GMC and drags the formula down, before replacing the formula with values. Could you try clearing all values in column J of GMC and run the code again? The code is dynamic to calculate the last used row and then apply the formula with this variable.

 

[JackDanIce]

As a helpful hint, you should try to avoid using .Select or .Activate as it slows your code down and can cause problems: Power Excel vba secret, avoid using select
You've coded in C++ so you should understand about objects which is what VBA uses and you can operate on objects without necessarily selecting them.

Your loop is open so each time it passes, you're testing to see if it should stop. Using a FOR LOOP fixes the end point so removes the need to test for exit on each pass. To determine the last used cell in a column, consider:

Range("A" & Rows.Count).End(xlUp).Row

Rows.Count = number of rows in the sheet so Range("A" & Rows.Count) = Range("A1048576"), i.e. the last cell in column A (Excel 2007+, Excel 2003 has less, can't remember the actual number)
.End(xlUp) 'jumps' up until it finds a non-empty cell, i.e. the last used one in the column, regardless of any empty ones above it.
.Row returns the row number of that non-empty cell so in a FOR LOOP, you could have:

Dim LR as Long, x as Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For x = 2 to LR
  Range("A" & x).Formula = "=B" & x & "+C" & x
  Range("A" & x).Value = Range("A" & x).Value
Next x

Here you're populating column A with a simple addition formula without selecting the cell, then replacing the formula in the cell with the value of the cell. Hope that helps.