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...

 

[Andrew Poulsom]

It sounds like you can use a lookup formula:

http://www.contextures.com/xlFunctions02.html

 

[SBAlexanian]

As a note.... The problem I have is that it won't loop.... It just does the first part then ends the sub.

 

[Smitty]

Welcome to the Board!

Is Access an option? It's generally a better choice for inventory management, and it's a true multi-user environment.

 

[SBAlexanian]

It sounds like you can use a lookup formula:

http://www.contextures.com/xlFunctions02.html

The problem is that the parts list is dynamic and our sales team is kind of slow with computers.... So I am trying to make this as easy as possible for them. Rather than having them using a lookup formula and modifying a lookup table, I want them to be able to punch in numbers for their respective parts and just be done with it, though I do really appreciate a prompt reply.

 

[SBAlexanian]

Welcome to the Board!

Is Access an option? It's generally a better choice for inventory management, and it's a true multi-user environment.

Unfortunately Access is not an option. Only the engineering team has access on their computers. And this little amount of data doesn't warrant an access database. We're talking about a constantly changing list of parts that never really exceeds 30 pieces.

 

[cboshdave]

Without having a little more detail, are you moving to another sheet and throwing off the check for the blank cell? I recommend trying to format the code so you are not actually moving around. Just reference the other sheets. Easy to say... harder to do.

 

[JackDanIce]

Try:

Sub GMC_Rebates1()

Dim j As Long

Application.ScreenUpdating = False

Sheets("GMC").Visible = xlSheetVisible
Sheets("GMC Rebates").Visible = xlSheetVisible

j = Sheets("GMC Rebates").Range("A" & Rows.Count).End(xlUp).Row

With Sheets("GMC")
    With .Range("J2:J" & Range("D" & Rows.Count).End(xlUp).Row)
        .Formula = "=IFERROR(VLOOKUP(D2,'GMC Rebates'!$A$1:$B$" & j & ",2,0),""No match found"")"
        .FillDown
        .Value = .Value
    End With
    If ActiveSheet.name <> .name Then .Select
    Application.Goto .Range("A1"), True
End With

Application.ScreenUpdating = True

End Sub

 

[SBAlexanian]

Without having a little more detail, are you moving to another sheet and throwing off the check for the blank cell? I recommend trying to format the code so you are not actually moving around. Just reference the other sheets. Easy to say... harder to do.

I am gonna do my best here to provide a bit more detail:

There is a part description/number in the "GMC" sheet in column D starting in row 2 and on.

There is the same description in the "GMC Rebates" Sheet in Column A Starting in row 2 and on.

I need the code to take each part description in the "GMC" sheet, find it in the "GMC Rebates" sheet, move over one column to get it's associated rebate value, jump back to the "GMC" sheet, paste the paste the value in column R of the same row, then move on and do it for the rest of the parts in the "GMC" sheet.

Hope that helps a bit... I tried to be clear on that description.

 

[SBAlexanian]

Try:

Sub GMC_Rebates1()

Dim j As Long

Application.ScreenUpdating = False

Sheets("GMC").Visible = xlSheetVisible
Sheets("GMC Rebates").Visible = xlSheetVisible

j = Sheets("GMC Rebates").Range("A" & Rows.Count).End(xlUp).Row

With Sheets("GMC")
    With .Range("J2:J" & Range("D" & Rows.Count).End(xlUp).Row)
        .Formula = "=IFERROR(VLOOKUP(D2,'GMC Rebates'!$A$1:$B$" & j & ",2,0),""No match found"")"
        .FillDown
        .Value = .Value
    End With
    If ActiveSheet.name <> .name Then .Select
    Application.Goto .Range("A1"), True
End With

Application.ScreenUpdating = True

End Sub

Thanks for the reply. Tried the code.... It is not looping, nor is it working for even the first part.... That level of coding is a bit out of my league to comprehend and try to modify to work.