Assigning cell values with VB

[cathywoodford]

Hi. I have inherited a huge macro from someone and now I'm trying to
manipulate it to suit my needs. I want to insert 2 rows at the top
of a spreadsheet and insert values into cells A1:AV1 and A2:AV2. I
would like to follow the same coding techniques as the original coder
so that it all looks the same. Here is a sample of code where he
assisgned column names to each column. I would like to do the same
but with cell's instead.
Sub NAME_COLUMNS()

'Declare Local Variables
Dim LAST_COL As String
Dim NO_ROWS As Integer
Dim C_WBK As String
Dim C_SHT As String

'Declare Column Name Variables
Dim COL_A As String
Dim COL_B As String
Dim COL_C As String
Dim COL_D As String
Dim COL_E As String
Dim COL_F As String
Dim COL_G As String
Dim COL_H As String
Dim COL_I As String
Dim COL_J As String
Dim COL_K As String
Dim COL_L As String
Dim COL_M As String
Dim COL_N As String
Dim COL_O As String
Dim COL_P As String
Dim COL_Q As String
Dim COL_R As String
Dim COL_S As String
Dim COL_T As String
Dim COL_U As String
Dim COL_V As String
Dim COL_W As String
Dim COL_X As String
Dim COL_Y As String
Dim COL_Z As String
Dim COL_AA As String
Dim COL_AB As String
Dim COL_AC As String
Dim COL_AD As String
Dim COL_AE As String
Dim COL_AF As String
Dim COL_AG As String
Dim COL_AH As String
Dim COL_AI As String
Dim COL_AJ As String
Dim COL_AK As String
Dim COL_AL As String
Dim COL_AM As String
Dim COL_AN As String
Dim COL_AO As String
Dim COL_AP As String
Dim COL_AQ As String
Dim COL_AR As String
Dim COL_AS As String
Dim COL_AT As String
Dim COL_AU As String
Dim COL_AV As String

Select Case C_FILE 'C_FILE Variable Assign in vb_Format_PlanX Modules
Case PLAN1
LAST_COL = "Y"
NO_ROWS = PLAN1_NO_ROWS
C_WBK = C_FILE + ".txt"
C_SHT = C_FILE
COL_A = "ACT" 'Activity Id
COL_B = "BLANK1" 'MaintPlan + MaintItem + CallNo : NOT
IMPORTED
COL_C = "OTYP" 'Work Order Type
COL_D = "TAG" 'Sort Field
COL_E = "TITLE" 'Short Text
COL_F = "MWC" 'Main Work Centre
COL_G = "BSD" 'Basicv Start Date
COL_H = "SYS" 'System No.
COL_I = "PRIO" 'SAP Priority
COL_J = "MOD" 'Module No.
COL_K = "PGRP" 'Maintenance Planning Group
COL_L = "LOG11" 'Maintenance Plan No.
COL_M = "LOG12" 'Maintenance Item No.
COL_N = "LOG13" 'Call No.
COL_O = "LOG14" 'Frequency
COL_P = "BLANK2" 'Functional Location - Imported as WBS code :
NOT IMPORTED
COL_Q = "MAT" 'Maintenance Activity Type
COL_R = "SYSC" 'System Condition
COL_S = "CAL" 'Calendar
COL_T = "LVPR" 'Leveling Priority
COL_U = "ES" 'P3 Early Start Date
COL_V = "ECON" 'P3 ES Constraint Type
COL_W = "ECOND" 'P3 ES Constraint Date
COL_X = "LCON" 'P3 LF Constraint Type
COL_Y = "LCOND" 'P3 LF Constraint Date
Case PLAN2
LAST_COL = "K"
NO_ROWS = PLAN2_NO_ROWS
C_WBK = C_FILE + ".txt"
C_SHT = C_FILE
COL_A = "ACT" 'Activity Id
COL_B = "BLANK1" 'MaintPlan + MaintItem + CallNo : NOT
IMPORTED
COL_C = "RES" 'Resource
COL_D = "BQ" 'Budget Quantity
COL_E = "BLANK2" 'Unit of Measure : NOT IMPORTED
COL_F = "TECH" 'Maximum No. of Technician
COL_G = "RCD" 'Resource Duration
COL_H = "UPT" 'Units Per Time Period
COL_I = "BLANK3" 'Maintenance Planning Group copied in
vb_Copy_WO_Data : NOT IMPORTED
COL_J = "BLANK4" 'System Condition copies in vb_Copy_WO_Data :
NOT IMPORTED
COL_K = "BLANK5" 'Main Work Centre copies in vb_Copy_WO_Data :
NOT IMPORTED
Case PLAN3
LAST_COL = "AV"
NO_ROWS = PLAN3_NO_ROWS
C_WBK = C_FILE + ".txt"
C_SHT = C_FILE
'COL_A = "BLANK1" 'SAP Id No. : NOT IMPORTED
COL_B = "ACT" 'Activity Id
COL_C = "OTYP" 'Work Order Type
COL_D = "TAG" 'Equipment TAG No.
COL_E = "TITLE" 'Desc
COL_F = "BSD" 'Basic Start Date
COL_G = "BFD" 'Basic Finish Date
COL_H = "SYS" 'System No.
COL_I = "PRIO" 'SAP Priority
COL_J = "MOD" 'Location Code
COL_K = "PGRP" 'Maintenance Planning Group
COL_L = "LOG11" 'Maintenance Plan No.
COL_M = "LOG13" 'Call No.
COL_N = "LOG14" 'Frequency
COL_O = "FLOC" 'Functional Location
COL_P = "LOG9" 'User Status
COL_Q = "MAT" 'Maintenance Activity Type
COL_R = "REV" 'Revision Code
COL_S = "LOG10" 'System Status
COL_T = "BLANK3" 'SAP ES Constraint Type : NOT IMPORTED
COL_U = "BLANK4" 'SAP ES Constraint Date : NOT IMPORTED
COL_V = "BLANK5" 'SAP LF Constraint Type : NOT IMPORTED
COL_W = "BLANK6" 'SAP LF Constraint Date : NOT IMPORTED
COL_X = "MWC" 'Main Work Centre
COL_Y = "SYSC" 'System Condition
COL_Z = "WWBS" 'Work Breakdown
COL_AA = "ECON" 'P3 ES Constraint Type
COL_AB = "ECOND" 'P3 ES Constraint Date
COL_AC = "LCON" 'P3 LF Constraint Type
COL_AD = "LCOND" 'P3 LF Constraint Date
COL_AE = "CAL" 'Calendar
COL_AI = "WO" 'SAP Work Order No.
COL_AJ = "ES" 'P3 Early Start Date
COL_AK = "EF" 'P3 Early Finish Date
COL_AN = "LVPR" 'Leveling Priority
COL_AP = "WOST" 'Work Order Status
COL_AQ = "MATI"
COL_AR = "SAFT"
COL_AS = "HVEN"
COL_AU = "DELETE" 'Delete P3 Activity
COL_AV = "BLANK8" 'Flag Modified Work Orders : NOT IMPORTED
Case PLAN4
LAST_COL = "M"
NO_ROWS = PLAN4_NO_ROWS
C_WBK = C_FILE + ".txt"
C_SHT = C_FILE
COL_A = "BLANK1" 'SAP Id No. : NOT IMPORTED
COL_B = "ACT" 'Activity Id No.
COL_C = "RES" 'Resource
COL_D = "BQ" 'Budget Quantity
COL_E = "BLANK2" 'Unit of Measure : NOT IMPORTED
COL_F = "TECH" 'Maximum No. of Technician
COL_G = "RCD" 'Resource Duration
COL_H = "UPT" 'Unit Per Time Period
COL_I = "BLANK3" 'Maintenance Planning Group : NOT IMPORTED
COL_J = "BLANK4" 'SAP Work Order No. : NOT IMPORTED
COL_K = "BLANK5" 'System Condition : NOT IMPORTED
COL_L = "DELETE" 'Delete Resource
COL_M = "BLANK7" 'Flag : NOT IMPORTED
End Select

With Sheets(C_SHT)
'Loops through Columns
For Each COL In .Columns("A:" + Trim(LAST_COL))

'Names Columns
With Workbooks(C_WBK).Names

'Assigns Current Column No. to Variable
C_COL = Right(COL.Address, (Len(COL.Address) - InStr(2,
COL.Address, "$")))

'Assigns Column Names
Select Case C_COL
Case "A"
.Add Name:=COL_A, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "B"
.Add Name:=COL_B, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "C"
.Add Name:=COL_C, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "D"
.Add Name:=COL_D, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "E"
.Add Name:=COL_E, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "F"
.Add Name:=COL_F, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "G"
.Add Name:=COL_G, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "H"
.Add Name:=COL_H, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "I"
.Add Name:=COL_I, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "J"
.Add Name:=COL_J, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "K"
.Add Name:=COL_K, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "L"
.Add Name:=COL_L, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "M"
.Add Name:=COL_M, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "N"
.Add Name:=COL_N, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "O"
.Add Name:=COL_O, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "P"
.Add Name:=COL_P, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "Q"
.Add Name:=COL_Q, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "R"
.Add Name:=COL_R, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "S"
.Add Name:=COL_S, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "T"
.Add Name:=COL_T, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "U"
.Add Name:=COL_U, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "V"
.Add Name:=COL_V, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "W"
.Add Name:=COL_W, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "X"
.Add Name:=COL_X, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "Y"
.Add Name:=COL_Y, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "Z"
.Add Name:=COL_Z, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "AA"
.Add Name:=COL_AA, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "AB"
.Add Name:=COL_AB, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "AC"
.Add Name:=COL_AC, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "AD"
.Add Name:=COL_AD, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "AE"
.Add Name:=COL_AE, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
'Case "AF"
'.Add Name:=COL_AF, RefersTo:="=" + C_SHT +
"!" + Trim(COL.Address)
'Case "AG"
'.Add Name:=COL_AG, RefersTo:="=" + C_SHT +
"!" + Trim(COL.Address)
'Case "AH"
'.Add Name:=COL_AH, RefersTo:="=" + C_SHT +
"!" + Trim(COL.Address)
Case "AI"
.Add Name:=COL_AI, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "AJ"
.Add Name:=COL_AJ, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "AK"
.Add Name:=COL_AK, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
'Case "AL"
'.Add Name:=COL_AL, RefersTo:="=" + C_SHT +
"!" + Trim(COL.Address)
'Case "AM"
'.Add Name:=COL_AM, RefersTo:="=" + C_SHT +
"!" + Trim(COL.Address)
Case "AN"
.Add Name:=COL_AN, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
'Case "AO"
'.Add Name:=COL_AO, RefersTo:="=" + C_SHT +
"!" + Trim(COL.Address)
Case "AP"
.Add Name:=COL_AP, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "AQ"
.Add Name:=COL_AQ, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "AR"
.Add Name:=COL_AR, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "AS"
.Add Name:=COL_AS, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
'Case "AT"
'.Add Name:=COL_AT, RefersTo:="=" + C_SHT +
"!" + Trim(COL.Address)
Case "AU"
.Add Name:=COL_AU, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
Case "AV"
.Add Name:=COL_AV, RefersTo:="=" + C_SHT + "!"
+ Trim(COL.Address)
End Select
End With
Next COL
End With

End Sub

Hope someone can help. Thanks,
Cathy

 

[Jim Cone]

Here is an example of one way to fill a row with data.
You can expand it to as many elements (cells) as needed.
'--
Dim vAcrossTop as Variant
vAcrossTop = Array("Alabama", "Alaska", " Arizona ", "Arkansas", " California")
Range("A1:E1").Value = vAcrossTop
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


<[email protected]>
wrote in message
Hi. I have inherited a huge macro from someone and now I'm trying to
manipulate it to suit my needs. I want to insert 2 rows at the top
of a spreadsheet and insert values into cells A1:AV1 and A2:AV2. I
would like to follow the same coding techniques as the original coder
so that it all looks the same. Here is a sample of code where he
assisgned column names to each column. I would like to do the same
but with cell's instead.
-snip-
Hope someone can help. Thanks,
Cathy

 

[cathywoodford]

Thanks for the reply but I need to skip some columns in between. For
example, A1, B1, C1, E1, F1, J1...

Any idea?

Thanks.
Cathy

 

[Jim Cone]

'--
Sub SkipSome()
Dim vAcrossTop As Variant
Dim vColumns As Variant
Dim N As Long

vAcrossTop = Array("Alabama", "Alaska", " Arizona ", "Arkansas", _
"California", "Canada")
vColumns = Array(1, 2, 3, 5, 6, 10)

For N = LBound(vColumns) To UBound(vColumns)
Cells(1, vColumns(N)).Value = vAcrossTop(N)
Next
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




<[email protected]>
wrote in message
Thanks for the reply but I need to skip some columns in between. For
example, A1, B1, C1, E1, F1, J1...
Any idea?
Thanks.
Cathy