Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Sub review
Hi there,
I am embarrassed to ask but I cannot get this simple macro to work. I want to copy some scattered cells from Sheet "Questionnaire" to sheet "Database"... I would like to transpose a certain range to the "database"sheet as well ... though it should stop when: If Sheets("Questionnaire").Range(Cells(12, 2 + icol), Cells(12, 2 + icol)).Value = "" Then = this turns into an application defined or object defined error though. I cannot see why though ... Probably for the same reason that it bugs on: Sheets("Questionnaire").Range(Cells(12, 2 + icol), Cells(33, 2 + icol)).Copy the range I would like to transpose into the "database"-sheet. Hope someone can help, Sige Here is my sub: Sub Macro10() Dim Lr As Long Dim icol As Long Dim nrKoloms As Long Lr = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row nrKoloms = Application.WorksheetFunction.CountA(Sheets("Quest ionnaire").Range("C12:L12")) MsgBox nrKoloms For icol = 1 To nrKoloms Step 1 If Sheets("Questionnaire").Range(Cells(12, 2 + icol), Cells(12, 2 + icol)).Value = "" Then Exit Sub End If Sheets("Database").Range("A" & Lr + icol - 1).Formula = "=Questionnaire!C4" Sheets("Database").Range("B" & Lr + icol - 1).Formula = "=Questionnaire!B6" Sheets("Database").Range("C" & Lr + icol - 1).Formula = "=Questionnaire!F5" Sheets("Database").Range("D" & Lr + icol - 1).Formula = "=Questionnaire!F6" Sheets("Database").Range("E" & Lr + icol - 1).Formula = "=Questionnaire!L4" Sheets("Database").Range("F" & Lr + icol - 1).Formula = "=Questionnaire!L6" Sheets("Questionnaire").Range(Cells(12, 2 + icol), Cells(33, 2 + icol)).Copy Sheets("Database").Range("G" & Lr).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Application.CutCopyMode = False Sheets("Database").Range("AC" & Lr + icol - 1).Formula = "=Questionnaire!C38" Sheets("Database").Range("AD" & Lr + icol - 1).Formula = "=Questionnaire!C40" Sheets("Database").Range("AE" & Lr + icol - 1).Formula = "=Questionnaire!C41" Sheets("Database").Range("AF" & Lr + icol - 1).Formula = "=Questionnaire!C42" Sheets("Database").Range("AG" & Lr + icol - 1).Formula = "=Questionnaire!C43" Sheets("Database").Range("AH" & Lr + icol - 1).Formula = "=Questionnaire!B29" Next icol End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Sub review
Sub Macro10()
Dim Lr As Long Dim icol As Long Dim nrKoloms As Long Lr = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row nrKoloms = Application.WorksheetFunction.CountA(Sheets("Quest ionnaire").Range("C12:L12")) Application.ScreenUpdating = False On Error Resume Next For icol = 1 To nrKoloms Step 1 If Sheets("Questionnaire").Cells(12, 2 + icol).Value = "" Then Exit Sub Else Sheets("Database").Range("A" & Lr + icol - 1) = Sheets("Questionnaire").Range("C4") Sheets("Database").Range("B" & Lr + icol - 1) = Sheets("Questionnaire").Range("B6") Sheets("Database").Range("C" & Lr + icol - 1) = Sheets("Questionnaire").Range("F5") Sheets("Database").Range("D" & Lr + icol - 1) = Sheets("Questionnaire").Range("F6") Sheets("Database").Range("E" & Lr + icol - 1) = Sheets("Questionnaire").Range("L4") Sheets("Database").Range("F" & Lr + icol - 1) = Sheets("Questionnaire").Range("L6") Sheets("Questionnaire").Activate ActiveSheet.Range(Cells(12, 2 + icol), Cells(33, 2 + icol)).Copy Sheets("Database").Range("G" & Lr + icol - 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Application.CutCopyMode = False Sheets("Database").Range("AC" & Lr + icol - 1) = Sheets("Questionnaire").Range("C38") Sheets("Database").Range("AD" & Lr + icol - 1) = Sheets("Questionnaire").Range("C40") Sheets("Database").Range("AE" & Lr + icol - 1) = Sheets("Questionnaire").Range("C41") Sheets("Database").Range("AF" & Lr + icol - 1) = Sheets("Questionnaire").Range("C42") Sheets("Database").Range("AG" & Lr + icol - 1) = Sheets("Questionnaire").Range("C43") Sheets("Database").Range("AH" & Lr + icol - 1) = Sheets("Questionnaire").Range("B29") End If Next icol Application.ScreenUpdating = False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
review changes in workbook | Excel Discussion (Misc queries) | |||
Code Review | Excel Programming | |||
i have a fax sent to me for review but i cant open its saying i n | New Users to Excel | |||
excel review bar | Excel Discussion (Misc queries) | |||
Review Tool bar | Excel Discussion (Misc queries) |