Hi everyone,
I am new to programming in visual and excel. Have programmed in other
languages. Anyways I have a user form that I prompt the user to enter
certain values. Based on that I open the workbooks I need to create my
report there is a possibly of 3-4 workbooks with one of them being the one I
am trying to dump all the data into. The main one I am trying to update is
Form1 and I have an english version of the form and a french version of the
form. The data is linked by 2-3 other workbooks. An auto model grid and a
ccs or dcs file depending if there is one for that dlr/rep. I have the code
written in my command1button click and this is what I have so far but for
some reason my for each will not work. What I am trying to do is look on
the 'Auto Model Grid' in col L which has rows 2 - 55. if the field has a ccs
code I want to search for the value in the ccs workbook if it is a dcs code I
want to search in the dcs workbook. I link the Auto Model Grid by looking at
the value in column B of the Auto Model Grid and searching for it's
equivalent in either the dcs or rcs workbooks col A. If they match I want
to copy the value from column 7 in the dcs or rcs workbook into column 14 of
the Auto Model Grid. Then I will eventually dump that value matching colA
from Auto Model Grid with ColA of the Form 1 sheet. This is a copy of the
code I have so far. If anyone can help, I would gratefully appreciate it. I
just seem to be stuck on this.
Thanks in advance
HeatherO
Private Sub CommandButton1_Click()

Dim Dlrno As String
Dim Repno As String
Dim RSM As Integer
Dim Dlrshp As String
Dim PrepFor As String
Dim dlrrep As String
Dim ccswrkbk As Workbook
Dim dcswrkbk As Workbook
Dim wks As Worksheet
Dim rngccs As Range
Dim rngdcs As Range
Dim rngtouse As Range
Dim mycell As Range
Dim mainwks As Worksheet
Dim sPath As String
Dim sMyFile As String
Dim ccsfile As String
Dim dcsfile As String
ccsfile = "no"
dcsfile = "no"


dlrrep = UserForm1.Dlrno + UserForm1.Repno


'Activate the correct worksheet based on English or French.
If EnglishButton1 Then

Workbooks("Form1").Worksheets("English").Activate
With ActiveSheet
.Range("B1").Value = UserForm1.Dlrno
.Range("B2").Value = UserForm1.Repno
.Range("B1:B2").NumberFormat = "General"
.Range("B3").Value = UserForm1.PrepFor
.Range("B4").Value = UserForm1.Dlrshp
.Range("B5").Value = UserForm1.RSM
End With
Set mainwks = Workbooks("Form1").Worksheets("English")

ElseIf frenchbutton1 Then


Workbooks("Form1").Worksheets("French").Activate
With ActiveSheet
.Range("B1").Value = UserForm1.Dlrno
.Range("B2").Value = UserForm1.Repno
.Range("B1:B2").NumberFormat = "General"
.Range("B3").Value = UserForm1.PrepFor
.Range("B4").Value = UserForm1.Dlrshp
.Range("B5").Value = UserForm1.RSM
End With
Set mainwks = Workbooks("Form1").Worksheets("French")

End If

'get values for both CCS and DCS and store in column "N" of Auto Model
Grid

Set wks = Workbooks("Auto Model Grid").Worksheets("sheet1")


sPath = "C:\Documents and Settings\My Documents\"
sMyFile = "CCS" & dlrrep & ".xls"
If Dir(sPath & MyFile) = "" Then
ccsfile = "yes"
Else: ccsfile = "no"
End If

sMyFile = "DCS" & dlrrep & ".xls"
If Dir(sPath & MyFile) = "" Then
dcsfile = "yes"
Else: dcsfile = "no"
End If


If ccsfile = "yes" Then
Set ccswrkbk = Workbooks.Open("C:\Documents and Settings\My
Documents\CCS" & dlrrep & ".xls")

With ccswrkbk.Worksheets("SMART")
Set rngccs = .Range("A2:H82")
End With
End If

If dcsfile = "yes" Then
Set dcswrkbk = Workbooks.Open("C:\Documents and Settings\My
Documents\DCS" & dlrrep & ".xls")
With dcswrkbk.Worksheets("SMART")
Set rngdcs = .Range("A2:H82")
End With
End If



For Each mycell In wks.Range("A2:N55").Cells
'check 10 columns over (column L)
If UCase(mycell.Offset(0, 10).Value) = "CCS" Then
Set rngtouse = rngccs
Else
Set rngtouse = rngdcs
End If

res = Application.VLookup(mycell.Value, rngtouse, 7, False)
If IsError(res) Then
'not found, put error msg in column 0 (.offset(0,15)
mycell.Offset(0, 15).Value = "not found"
Else
mycell.Offset(0, 15).Value = res
End If
Next mycell



UserForm1.Hide
Application.Visible = True

End

End Sub